Re: PERFORM not working properly, please help.. - Mailing list pgsql-general

From Florent THOMAS
Subject Re: PERFORM not working properly, please help..
Date
Msg-id 1266576749.17707.1.camel@Dell_Inspiron
Whole thread Raw
In response to Re: PERFORM not working properly, please help..  (Pavel Stehule <pavel.stehule@gmail.com>)
List pgsql-general
And what about that : http://www.postgresql.org/docs/8.4/interactive/xfunc-sql.html#XFUNC-SQL-FUNCTIONS-RETURNING-SET

Maybe my french english disallowed me to understand right the question, but I think that this item could help in a way!


Le vendredi 19 février 2010 à 11:04 +0100, Pavel Stehule a écrit :
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..
>  > >
>
>

pgsql-general by date:

Previous
From: Magnus Hagander
Date:
Subject: Re: How to get the users name from users group?
Next
From: Pavel Stehule
Date:
Subject: Re: PERFORM not working properly, please help..