Обсуждение: help with function
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.
Hello > EXECUTE 'UPDATE sale SET comments = ' || Use PERFORM instead Alexey
> 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