[DOCS] PostgreSQL 7.4 Documentation - PL/pgSQL

[Halley Pacheco de Oliveira]

In PL/pgSQL - SQL Procedural Language, 37.6.2. SELECT
INTO, is written:

Here is an example that handles the case where no rows
have been returned:

DECLARE
users_rec RECORD;
full_name varchar;
BEGIN
SELECT INTO users_rec * FROM users WHERE
user_id=3;

IF users_rec.homepage IS NULL THEN
-- user entered no homepage, return "http://";
RETURN ''http://'';
END IF;
END;

In the example before this no rows were returned. This
is an example of NULL result, not of NOT FOUND. The 
comment says "-- user entered no homepage".

In "37.8.3.3. Returning Cursors" is written:



But I'm getting;



Regards,
Halley


__

Participe da pesquisa global sobre o Yahoo! Mail: 
http://br.surveys.yahoo.com/global_mail_survey_br

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [DOCS] UPDATE FROM syntax

[Bruce Momjian]

Paul Makepeace wrote:
> Hi, is there any documentation for Pg's UPDATE FROM (apparently
> non-standard) syntax? It is mentioned briefly in \h update and not at
> all in http://www.postgresql.org/docs/7.3/static/dml-update.html
> or http://www.postgresql.org/docs/7.3/static/queries-table-expressions.html
> (directly).

Yea, it is kind of special for UPDATE, so we just have it in the UPDATE
manual page.

> [moving a bit OT for docs?]
> 
> I solved my own problem but was still curious since it's non-standard if
> there's a "more standard" way of updating a table from another query.
> 
> ALTER TABLE components ADD COLUMN default_body TEXT;
> 
> UPDATE components SET default_body = bb.body
> FROM (SELECT b.body, cpb.component_id
> FROM componentspagesbodies cpb NATURAL JOIN bodies b
>WHERE cpb.page_id IS NULL) AS bb
> WHERE bb.component_id = components.component_id;
> 
> (Incidently, I tried UPDATE components c SET ... c.component_id but the
> parser wasn't happy with it.)

No, there really isn't.  The only standard way I know of is to use a
subselect in the SET clause:

SET val = (SELECT ...)

The big downside of this is the the SELECT is run for every query, and
it can't be used as part of the WHERE clause.  (You would need another
subquery there.)

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org