From: lacou <lacouf@gmail.com>
To: pgsql-sql@postgresql.org
Subject: help with function
Date: Thu, 16 Mar 2006 10:06:16 -0500
Message-ID: <dvbutn$8ht$1@sea.gmane.org> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: lacouf@gmail.com
Subject: Re: help with function
In-Reply-To: <dvbutn$8ht$1@sea.gmane.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox