pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHelp with function
7+ messages / 5 participants
[nested] [flat]
* Help with function
@ 2004-09-20 20:36 CHRIS HOOVER <CHRIS.HOOVER@companiongroup.com>
0 siblings, 1 reply; 7+ messages in thread
From: CHRIS HOOVER @ 2004-09-20 20:36 UTC (permalink / raw)
To: pgsql-sql
I need some help writing a simple function.
Due to some program limitations for a program I run the db's for, I'm having
to write some simple functions to run some selects. However, I am not sure
how to have them correctly return the record(s) selected and/or how to
properly call them from sql.
Would someone be so kind as to help me with this.
Here is an example function:
CREATE OR REPLACE FUNCTION "public"."test_func" (varchar) RETURNS SETOF
"public"."test_tbl" AS'
Declare
PCN varchar;
test_tbl_rec clmhdr%ROWTYPE;
Begin
PCN := $1;
select into test_tbl_rec * from test_tbl where test_tbl.pcn = PCN;
return test_tbl_rec;
end;
'LANGUAGE 'plpgsql' VOLATILE RETURNS NULL ON NULL INPUT SECURITY INVOKER;
I was trying to call this function from psql using:
select test_func('asdf');
Which returned:
ERROR: Set-valued function called in context that cannot accept a set
WARNING: Error occurred while executing PL/pgSQL function test_func
WARNING: while casting return value to function's return type
What am I doing wrong? I'm in fairly new territory with this type of
functions. I normally have just written simple trigger function that log
table changes.
Thanks for any and all help.
Chris
P.S.
Is there a good website and/or book for learning the intricacies of pgsql?
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Help with function
@ 2004-09-20 20:51 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: CHRIS HOOVER <CHRIS.HOOVER@companiongroup.com>
0 siblings, 0 replies; 7+ messages in thread
From: Stephan Szabo @ 2004-09-20 20:51 UTC (permalink / raw)
To: CHRIS HOOVER <CHRIS.HOOVER@companiongroup.com>; +Cc: pgsql-sql
On Mon, 20 Sep 2004, CHRIS HOOVER wrote:
> I need some help writing a simple function.
>
> Due to some program limitations for a program I run the db's for, I'm having
> to write some simple functions to run some selects. However, I am not sure
> how to have them correctly return the record(s) selected and/or how to
> properly call them from sql.
>
> Would someone be so kind as to help me with this.
>
> Here is an example function:
>
> CREATE OR REPLACE FUNCTION "public"."test_func" (varchar) RETURNS SETOF
> "public"."test_tbl" AS'
> Declare
> PCN varchar;
> test_tbl_rec clmhdr%ROWTYPE;
>
> Begin
>
> PCN := $1;
>
> select into test_tbl_rec * from test_tbl where test_tbl.pcn = PCN;
> return test_tbl_rec;
>
> end;
> 'LANGUAGE 'plpgsql' VOLATILE RETURNS NULL ON NULL INPUT SECURITY INVOKER;
If you want to return sets of rows, you're going to need to loop over the
returned rows from the select using return next.
These links may help:
http://www.varlena.com/varlena/GeneralBits/26.html
http://techdocs.postgresql.org/guides/SetReturningFunctions
> I was trying to call this function from psql using:
> select test_func('asdf');
As a side note, plpgsql set returning functions cannot be called in a
select list, only in the from clause (the above links will have examples).
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Help with function
@ 2004-09-21 14:23 CHRIS HOOVER <CHRIS.HOOVER@companiongroup.com>
0 siblings, 1 reply; 7+ messages in thread
From: CHRIS HOOVER @ 2004-09-21 14:23 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: pgsql-sql
Thanks a bunch for the pointers and help.
One other hopefully quick question.
How do you query using a variable containing the query?
I'm trying to build a select statment based upon what parameters are being
passed to the function.
somthing like this:
Declare
Param1 varchar;
Param2 varchar;
SQLStr varchar;
Table_rec Table%ROWTYPE;
Begin
SQLStr:="select * from table"
Param1:= $1;
Param2 :=$2;
if (Param1 is not null) then
SQLStr := SQLStr || "where column=Param1";
else
SQLStr := SQLStr || "where column=Param2";
end if;
SQLStr := SQLStr || ";"
for Table_Rec in SQLStr loop
return next Table_rec;
end loop;
return;
end;
Is this possible?
Thanks again for any help,
Chris
------------------( Forwarded letter 1 follows )---------------------
Date: Mon, 20 Sep 2004 13:51:09 -0700 (PDT)
To: chris.hoover
Cc: pgsql-sql@postgresql.org.comp
From: Stephan.Szabo[sszabo]@megazone.bigpanda.com.comp
Subject: Re: [SQL] Help with function
On Mon, 20 Sep 2004, CHRIS HOOVER wrote:
> I need some help writing a simple function.
>
> Due to some program limitations for a program I run the db's for, I'm having
> to write some simple functions to run some selects. However, I am not sure
> how to have them correctly return the record(s) selected and/or how to
> properly call them from sql.
>
> Would someone be so kind as to help me with this.
>
> Here is an example function:
>
> CREATE OR REPLACE FUNCTION "public"."test_func" (varchar) RETURNS SETOF
> "public"."test_tbl" AS'
> Declare
> PCN varchar;
> test_tbl_rec clmhdr%ROWTYPE;
>
> Begin
>
> PCN := $1;
>
> select into test_tbl_rec * from test_tbl where test_tbl.pcn = PCN;
> return test_tbl_rec;
>
> end;
> 'LANGUAGE 'plpgsql' VOLATILE RETURNS NULL ON NULL INPUT SECURITY INVOKER;
If you want to return sets of rows, you're going to need to loop over the
returned rows from the select using return next.
These links may help:
http://www.varlena.com/varlena/GeneralBits/26.html
http://techdocs.postgresql.org/guides/SetReturningFunctions
> I was trying to call this function from psql using:
> select test_func('asdf');
As a side note, plpgsql set returning functions cannot be called in a
select list, only in the from clause (the above links will have examples).
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Help with function
@ 2004-09-21 15:25 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: CHRIS HOOVER <CHRIS.HOOVER@companiongroup.com>
0 siblings, 0 replies; 7+ messages in thread
From: Stephan Szabo @ 2004-09-21 15:25 UTC (permalink / raw)
To: CHRIS HOOVER <CHRIS.HOOVER@companiongroup.com>; +Cc: pgsql-sql
On Tue, 21 Sep 2004, CHRIS HOOVER wrote:
> Thanks a bunch for the pointers and help.
>
> One other hopefully quick question.
>
> How do you query using a variable containing the query?
>
> I'm trying to build a select statment based upon what parameters are being
> passed to the function.
>
> somthing like this:
>
> Declare
> Param1 varchar;
> Param2 varchar;
> SQLStr varchar;
> Table_rec Table%ROWTYPE;
> Begin
>
> SQLStr:="select * from table"
> Param1:= $1;
> Param2 :=$2;
>
> if (Param1 is not null) then
> SQLStr := SQLStr || "where column=Param1";
> else
> SQLStr := SQLStr || "where column=Param2";
> end if;
> SQLStr := SQLStr || ";"
>
> for Table_Rec in SQLStr loop
> return next Table_rec;
> end loop;
> return;
>
> end;
>
> Is this possible?
Pretty much yes. You can use the
FOR <record> IN EXECUTE <sqlstring> LOOP
structure to run the query. The only thing is that
you have to put the values into the string not the name
of the parameters (probably using quote_literal).
So rather than
SQLStr := SQLStr || "where column = Param1";
you'd want something like:
SQLStr := SQLStr || "where column = " || quote_literal(Param1);
^ permalink raw reply [nested|flat] 7+ messages in thread
* help with function
@ 2006-03-16 15:06 lacou <lacouf@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: lacou @ 2006-03-16 15:06 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: help with function
@ 2006-03-16 15:48 Алексей Заяц <az@antora.ru>
parent: lacou <lacouf@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Алексей Заяц @ 2006-03-16 15:48 UTC (permalink / raw)
To: pgsql-sql
Hello
> EXECUTE 'UPDATE sale SET comments = ' ||
Use PERFORM instead
Alexey
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: help with function
@ 2006-03-16 15:49 Daniel Caune <daniel.caune@ubisoft.com>
0 siblings, 0 replies; 7+ messages in thread
From: Daniel Caune @ 2006-03-16 15:49 UTC (permalink / raw)
To: lacou <lacouf@gmail.com>; pgsql-sql
> 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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2006-03-16 15:49 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-09-20 20:36 Help with function CHRIS HOOVER <CHRIS.HOOVER@companiongroup.com>
2004-09-20 20:51 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-09-21 14:23 Re: Help with function CHRIS HOOVER <CHRIS.HOOVER@companiongroup.com>
2004-09-21 15:25 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2006-03-16 15:06 help with function lacou <lacouf@gmail.com>
2006-03-16 15:48 ` Re: help with function Алексей Заяц <az@antora.ru>
2006-03-16 15:49 Re: help with function Daniel Caune <daniel.caune@ubisoft.com>
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