Обсуждение: help with function

Поиск
Список
Период
Сортировка

help with function

От
lacou
Дата:
Hello,

I have 2 tables where each table has a column named "comments" and the 
tables are related as a one to many.  I want to concatenate all the 
comments of the many side to the one side so I wrote the following 
plpgsql function to do so.

<pre>
CREATE OR REPLACE FUNCTION fixcomments()  RETURNS int4 AS
$BODY$
DECLARE    mviews RECORD;    i int4;
BEGIN
    FOR mviews IN SELECT * FROM saleorder WHERE comments is not null 
and comments <> '' LOOP
        -- Now "mviews" has one record from saleorder
        EXECUTE 'UPDATE sale SET comments = ' || 
quote_ident(sale.comments) || quote_ident(mviews.comments)    || ' WHERE sale.id = ' || quote_ident(mviews.sale_id);
i:= i + 1;    END LOOP;
 
    RETURN i;
END;
$BODY$  LANGUAGE 'plpgsql' VOLATILE;

</pre>

I have the following error when I run this code:

<pre>
ERROR:  missing FROM-clause entry for table "sale"
CONTEXT:  SQL statement "SELECT  'UPDATE sale SET comments = ' || 
quote_ident(sale.comments) || quote_ident( $1 ) || ' WHERE sale.id = ' 
|| quote_ident( $2 )"
PL/pgSQL function "fixcomments" line 11 at execute statement
</pre>

Doesn anybody know what I am doing wrong here ?

Lacou.

Re: help with function

От
Алексей Заяц
Дата:
Hello
>          EXECUTE 'UPDATE sale SET comments = ' ||
Use PERFORM instead

Alexey

Re: help with function

От
"Daniel Caune"
Дата:
> Hello,
>
> I have 2 tables where each table has a column named "comments" and the
> tables are related as a one to many.  I want to concatenate all the
> comments of the many side to the one side so I wrote the following
> plpgsql function to do so.
>
> <pre>
> CREATE OR REPLACE FUNCTION fixcomments()
>    RETURNS int4 AS
> $BODY$
> DECLARE
>      mviews RECORD;
>      i int4;
> BEGIN
>
>      FOR mviews IN SELECT * FROM saleorder WHERE comments is not null
> and comments <> '' LOOP
>
>          -- Now "mviews" has one record from saleorder
>
>          EXECUTE 'UPDATE sale SET comments = ' ||
> quote_ident(sale.comments) || quote_ident(mviews.comments)
>         || ' WHERE sale.id = ' || quote_ident(mviews.sale_id);

EXECUTE 'UPDATE sale SET comments = ''' || quote_ident(sale.comments ||
mviews.comment) || ''' WHERE sale.id = ''' ||
quote_ident(mviews.sale_id) || '''';

Does that help?


>      i := i + 1;
>      END LOOP;
>
>      RETURN i;
> END;
> $BODY$
>    LANGUAGE 'plpgsql' VOLATILE;
>
> </pre>
>
> I have the following error when I run this code:
>
> <pre>
> ERROR:  missing FROM-clause entry for table "sale"
> CONTEXT:  SQL statement "SELECT  'UPDATE sale SET comments = ' ||
> quote_ident(sale.comments) || quote_ident( $1 ) || ' WHERE sale.id = '
> || quote_ident( $2 )"
> PL/pgSQL function "fixcomments" line 11 at execute statement
> </pre>
>
> Doesn anybody know what I am doing wrong here ?
>
> Lacou.
>
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 6: explain analyze is your friend