--=__Part466F4667.1__=
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: quoted-printable
As I wrote before you will have to define your fields when querying the =
function,
eg. select * from myfunction() as ("field1" integer, "field2" text, ...)
So suppose you have a table like this
CREATE TABLE sometable (
"id" serial,
"sometextfield" text,
"aninteger" int,
"andavarchar" varchar(20)
);
you would have to query the function below like this:
select * from myfunction() as ("id" integer, "sometextfield" text, =
"aninteger" integer, "andavarchar" varchar);
=20
You are however not obliged to use the same fieldnames as defined in the =
table definition, so this will work too:
select * from myfunction() as ("myid" integer, "textfield" text, "myint" =
integer, "varfield" varchar);
=20
Automatic type conversion will work too, so this is ok too
select * from myfunction() as ("myid" integer, "textfield" text, "myint" =
integer, "varfield" text);
=20
Now suppose you change the selecte statement in myfunction to
SELECT sometextfield, andavarchar FROM sometable WHERE id > 5
=20
Now myfunction will not return 4 fields but only 2 and thus you would =
query myfunction like this
select * from myfunction() as ("sometextfield" text, "andavarchar" =
varchar);
or=20
select * from myfunction() as ("textfield" text, "varfield" varchar);
or
select * from myfunction() as ("textfield" text, "varfield" text);
=20
So defining your function as RETURNS SETOF RECORD only postpones the =
moment you define your output fields.
=20
Either you define your output fields when creating your function, or (like
=
in the above example) when querying your function.
=20
=20
>>> "maria s" <psmg01@[EMAIL PROTECTED]
> 2008-06-03 15:12 >>>
Hi Bart,
I will not know the number of fields. Because it will vary .
Is there any solution for this?
Thanks,
Maria
>>> "maria s" <psmg01@[EMAIL PROTECTED]
> 2008-06-03 15:01 >>>
Hi Friends,
Thanks for all your for the reply.
I tried the function and when I execute it using
select * from myfunction()
it says=20
ERROR: a column definition list is required for functions returning =
"record"
Could you please help me to fix this error?
Thanks so much for your help.
-maria
On Tue, Jun 3, 2008 at 3:06 AM, Bart Degryse <Bart.Degryse@[EMAIL PROTECTED]
> =
wrote:
Hi Maria,
Try something like
CREATE OR REPLACE FUNCTION myfunction() RETURNS SETOF RECORD AS
$body$
DECLARE
rec record;
BEGIN
FOR rec IN (
SELECT * FROM sometable)
LOOP
RETURN NEXT rec;
END LOOP;
RETURN;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE;
=20
As you can see, the number and type of the output fields only depends on =
whatever table you query in the FOR loop.
It's not magic though. It just postpones defining the number and type of =
the output fields until querying the function.
You will have to define the output fields when querying your function, =
like
select * from myfunction() as ("field1" integer, "field2" text, ...)
>>> "maria s" <psmg01@[EMAIL PROTECTED]
> 2008-06-02 22:40 >>>
Hi friends,
I am very new to plsql.
I have to write a function that quries few tables and returns a resultset
=
of varying column.=20
In that case I cannot predefine the table with column.=20
If I use RETURNS SETOF then I should know the number of columns and its =
type?!=20
Is there anyway to return a resultset with any number of column?
Thanks for your help.
-maria
--=__Part466F4667.1__=
Content-Type: text/html; charset=US-ASCII
Content-Transfer-Encoding: quoted-printable
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Dutf-8">
<META content=3D"MSHTML 6.00.2800.1264" name=3DGENERATOR></HEAD>
<BODY style=3D"MARGIN: 4px 4px 1px; FONT: 10pt Tahoma">
<DIV>As I wrote before you will have to define your fields when querying =
the function,</DIV>
<DIV>eg. select * from myfunction() as ("field1" integer, "field2" text, =
....)<BR></DIV>
<DIV>So suppose you have a table like this</DIV>
<DIV>CREATE TABLE sometable (</DIV>
<DIV> "id" serial,</DIV>
<DIV> "sometextfield" text,</DIV>
<DIV> "aninteger" int,</DIV>
<DIV> "andavarchar" varchar(20)</DIV>
<DIV>);</DIV>
<DIV>you would have to query the function below like this:</DIV>
<DIV>select * from myfunction() as ("id" integer, "sometextfield" text, =
"aninteger" integer, "andavarchar" varchar);</DIV>
<DIV> </DIV>
<DIV>You are however not obliged to use the same fieldnames as defined in
=
the table definition, so this will work too:</DIV>
<DIV>
<DIV>select * from myfunction() as ("myid" integer, "textfield" text, =
"myint" integer, "varfield" varchar);</DIV>
<DIV> </DIV>
<DIV>Automatic type conversion will work too, so this is ok
too</DIV></DIV>=
<DIV>
<DIV>select * from myfunction() as ("myid" integer, "textfield" text, =
"myint" integer, "varfield" text);</DIV>
<DIV> </DIV>
<DIV>Now suppose you change the selecte statement in myfunction to</DIV>
<DIV>SELECT sometextfield, andavarchar FROM sometable WHERE id > =
5</DIV>
<DIV> </DIV>
<DIV>Now myfunction will not return 4 fields but only 2 and thus you would
=
query myfunction like this</DIV>
<DIV>
<DIV>select * from myfunction() as ("sometextfield"
text, "andavarchar=
" varchar);</DIV>
<DIV>or </DIV></DIV>
<DIV>
<DIV>select * from myfunction() as ("textfield" text, "varfield" =
varchar);</DIV>
<DIV>or</DIV>
<DIV>
<DIV>select * from myfunction() as ("textfield" text, "varfield" =
text);</DIV>
<DIV> </DIV>
<DIV>So defining your function as RETURNS SETOF RECORD only postpones the
=
moment you define your output fields.</DIV>
<DIV> </DIV>
<DIV>Either you define your output fields when creating your function, or
=
(like in the above example) when querying your function.</DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV>>>> "maria s" <psmg01@[EMAIL PROTECTED]
2008-06-03 15:12 =
>>><BR>Hi Bart,<BR>I will not know the number of fields. Because
=
it will vary .<BR>Is there any solution for
this?<BR><BR>Thanks,<BR>Maria<B=
R><BR>>>> "maria s" <psmg01@[EMAIL PROTECTED]
2008-06-03 15:01 =
>>><BR>Hi Friends,<BR>Thanks for all your for the reply.<BR><BR>I
=
tried the function and when I execute it using<BR>select * from
myfunction(=
)<BR>it says <BR>ERROR: a column definition list is required for =
functions returning "record"<BR><BR>Could you please help me to fix this =
error?<BR><BR>Thanks so much for your
help.<BR><BR>-maria<BR><BR></DIV></DI=
V></DIV></DIV>
<DIV class=3Dgmail_quote>On Tue, Jun 3, 2008 at 3:06 AM, Bart Degryse =
<<A
href=3D"mailto:Bart.Degryse@[EMAIL PROTECTED]
">Bart.Degryse@[EMAIL PROTECTED]
>> wrote:<BR>
<BLOCKQUOTE class=3Dgmail_quote style=3D"PADDING-LEFT: 1ex; MARGIN: 0pt =
0pt 0pt 0.8ex; BORDER-LEFT: rgb(204,204,204) 1px solid">
<DIV style=3D"MARGIN: 4px 4px 1px; FONT: 10pt Tahoma; font-size-adjust: =
none; font-stretch: normal">
<DIV>Hi Maria,</DIV>
<DIV>Try something like</DIV>
<DIV>CREATE OR REPLACE FUNCTION myfunction() RETURNS SETOF
RECORD AS</=
DIV>
<DIV>$body$</DIV>
<DIV>DECLARE</DIV>
<DIV> rec record;</DIV>
<DIV>BEGIN</DIV>
<DIV> FOR rec IN (</DIV>
<DIV> SELECT * FROM sometable)</DIV>
<DIV> LOOP</DIV>
<DIV> RETURN NEXT rec;</DIV>
<DIV> END LOOP;</DIV>
<DIV> RETURN;</DIV>
<DIV>END;</DIV>
<DIV>$body$</DIV>
<DIV>LANGUAGE 'plpgsql' VOLATILE;</DIV>
<DIV> </DIV>
<DIV>As you can see, the number and type of the output fields only depends
=
on whatever table you query in the FOR loop.</DIV>
<DIV>It's not magic though. It just postpones defining the number and type
=
of the output fields until querying the function.</DIV>
<DIV>You will have to define the output fields when querying your =
function, like</DIV>
<DIV>select * from myfunction() as ("field1" integer, "field2" text, =
....)<BR><BR>>>> "maria s" <<A
href=3D"mailto:psmg01@[EMAIL PROTECTED]
" =
target=3D_blank>psmg01@[EMAIL PROTECTED]
>> 2008-06-02 22:40 >>>
<DIV>
<DIV></DIV>
<DIV class=3DWj3C7c><BR>Hi friends,<BR>I am very new to plsql.<BR><BR>I =
have to write a function that quries few tables and returns a =
resultset of varying column. <BR><BR> In that case I cannot predefine
=
the table with column. <BR>If I use RETURNS SETOF then I should know the =
number of columns and its type?! <BR><BR>Is there anyway to return a =
resultset with any number of column?<BR><BR>Thanks for your
help.<BR><BR>-m=
aria<BR></DIV></DIV></DIV></DIV></BLOCKQUOTE></DIV><BR></BODY></HTML>
--=__Part466F4667.1__=--
|
