To: pgsql-sql@postgresql.org
From: lacou <lacouf@gmail.com>
Subject: help with function
Date: Thu, 16 Mar 2006 10:06:16 -0500
Lines: 47
Message-ID: <dvbutn$8ht$1@sea.gmane.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
User-Agent: Thunderbird 1.5 (Windows/20051201)

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.