Обсуждение: PERFORM not working properly, please help..

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!

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..

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..
>

On 18/02/2010 12:05, wilczarz1@op.pl wrote:

>
> CREATE OR REPLACE FUNCTION A3() RETURNS VOID AS $BODY$
> begin
>  perform A1();
> end;
> $BODY$ LANGUAGE 'plpgsql';

You need to do:

  select * from A1();

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

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..
>  > >
>
>

Hi Ray, thanks for reply. Your solution needs to be modified with alias to get executed properly:

CREATE OR REPLACE FUNCTION A3() RETURNS VOID AS $BODY$
begin
 select * from A1() as dummy ( x double precision );
 return;
end;
$BODY$ LANGUAGE 'plpgsql';

but when used: select * from A3() it generates error "query has no destination for result data". That was the reason to
usePERFORM in the first place.. 

"Raymond O'Donnell" <rod@iol.ie> napisał(a):
 > On 18/02/2010 12:05, wilczarz1@op.pl wrote:
 >
 > >
 > > CREATE OR REPLACE FUNCTION A3() RETURNS VOID AS $BODY$
 > > begin
 > >  perform A1();
 > > end;
 > > $BODY$ LANGUAGE 'plpgsql';
 >
 > You need to do:
 >
 >   select * from A1();
 >
 > Ray.
 >
 > --
 > Raymond O'Donnell :: Galway :: Ireland
 > rod@iol.ie

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.


"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..
 > >

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..
>  > >
>
>

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..
>  > >  > >
>  > >
>  > >
>
>

On 19/02/2010 09:59, wilczarz1@op.pl wrote:
> Hi Ray, thanks for reply. Your solution needs to be modified with alias to get executed properly:
>
> CREATE OR REPLACE FUNCTION A3() RETURNS VOID AS $BODY$
> begin
>  select * from A1() as dummy ( x double precision );
>  return;
> end;
> $BODY$ LANGUAGE 'plpgsql';
>
> but when used: select * from A3() it generates error "query has no destination for result data". That was the reason
touse PERFORM in the first place.. 

Yes, you're absolutely right - the returned data needs a destination. I
should have written:

  perform * from A1();

That ought to work too.

Ray.


--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

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!

"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..
 > >  > >
 > >
 > >