Re: PERFORM not working properly, please help..

2010/2/19  <wilczarz1@op.pl>:
> I suppose some workaround would be to introduce temporary cursor:
>
> CREATE OR REPLACE FUNCTION A3() RETURNS VOID AS $BODY$
> declare _tmp record;
> begin
>  select * from A1() as dummy ( x double precision ) into _tmp;
> end;
> $BODY$ LANGUAGE 'plpgsql';
>
> But I'm not sure if this is more effiecent than A3 returning the set. Thanks for replies!

Hard to say. Temporary tables needs changes in system dictionary - but
are not limited by RAM and you can create index and actualise
statistic. SRF (Set Returning Function) doesn't needs changes in
dictionary, but doesn't allow indexes. Any way has own plus and minus.
Personally I prefere SRF - when is possible - for to ten thousand rows
sets.

Regards
Pavel Stehule

>
> "Pavel Stehule" <pavel.stehule@gmail.com> napisał(a):
>  > 2010/2/19  <wilczarz1@op.pl>:
>  > > Hi Pavel, thanks for reply. Your solution:
>  > >
>  > > CREATE OR REPLACE FUNCTION A3() RETURNS VOID AS $BODY$
>  > > begin
>  > >  return query select * from A1();
>  > >  return;
>  > > end;
>  > > $BODY$ LANGUAGE 'plpgsql';
>  > >
>  > > generates error "cannot use RETURN QUERY in a non-SETOF function" because A3 returns VOID.
>  >
>  > problem is in A3, cannot be void.
>  >
>  > PostgreSQL has only function. It hasn't "procedures" where you can
>  > execute unbinded queries. So if you can take any result from any
>  > rutine, you have to take it explicitly. VOID in pg means, there are no
>  > any interesting result, really no any interesting result. It can be
>  > problem, when you know MySQL procedures or MSSQL procedures. You have
>  > to forgot on procedures with returning recordset or multirecordset as
>  > secondary effect.
>  >
>  > regards
>  > Pavel Stehule
>  >
>  > >
>  > >
>  > > "Pavel Stehule" <pavel.stehule@gmail.com> napisał(a):
>  > >  > Hello
>  > >  >
>  > >  > 2010/2/18  <wilczarz1@op.pl>:
>  > >  > > I have a function A1 that returns setof records, and I use it in two ways:
>  > >  > > 1) from function A2, where I need results from A1
>  > >  > > 2) from function A3, where I don't need these results, all I need is to
>  > >  > > execute logic from A1
>  > >  > >
>  > >  > > Here ale very simple versions of my functions:
>  > >  > >
>  > >  > > CREATE OR REPLACE FUNCTION A1() RETURNS setof record AS $BODY$
>  > >  > > begin
>  > >  > > Ă? -- some logic here
>  > >  > > Ă? return query select col from tab;
>  > >  > > end;
>  > >  > > $BODY$ LANGUAGE 'plpgsql';
>  > >  > >
>  > >  > > CREATE OR REPLACE FUNCTION A2() RETURNS setof record AS $BODY$
>  > >  > > begin
>  > >  > > Ă? -- some logic here
>  > >  > > Ă? return query select * from A1() as dummy ( x double precision);
>  > >  > > end;
>  > >  > > $BODY$ LANGUAGE 'plpgsql';
>  > >  > >
>  > >  > > CREATE OR REPLACE FUNCTION A3() RETURNS VOID AS $BODY$
>  > >  > > begin
>  > >  > > Ă? perform A1();
>  > >  > > end;
>  > >  > > $BODY$ LANGUAGE 'plpgsql';
>  > >  > >
>  > >  > > And here are my function calls:
>  > >  > > select * from A1() as(x double precision) --ok
>  > >  > > select * from A2() as(x double precision) --ok
>  > >  > > select * from A3(); --not ok, argh!
>  > >  > >
>  > >  >
>  > >  > it is correct. Every function has own stack for result. There are not
>  > >  > some global stack. Perform just run function and doesn't copy inner
>  > >  > result's stack to outer result stack.
>  > >  >
>  > >  > your A3 function have to be
>  > >  > begin
>  > >  >   return query select * from a1
>  > >  >   return;
>  > >  > end;
>  > >  >
>  > >  > like a2 function
>  > >  >
>  > >  > regards
>  > >  > Pavel Stehule
>  > >  > > The last one generates error "set-valued function called in context that
>  > >  > > cannot accept a set". Why doesn't PERFORM work here? Thanks for help..
>  > >  > >
>  > >
>  > >
>
>