How to call SETOF function?

On Wednesday 06 January 2010 5:01:39 pm Iain Barnett wrote:

If I run the following (in either a terminal or the PgAdmin3 Query tool) I
get the error:

ERROR: query has no destination for result data
SQL state: 42601
Hint: If you want to discard the results of a SELECT, use PERFORM instead.
Context: PL/pgSQL function "anything_all_udf" line 3 at SQL statement

How am I'm supposed to call the function so that I get the same result as
the adhoc query it surrounds?

----

create table anything (
first serial primary key not null
, second varchar not null
, third timestamp default now() not null
);

insert into anything ( second ) values ( 'row a' );
insert into anything ( second ) values ( 'row b' );
insert into anything ( second ) values ( 'row c' );

CREATE OR REPLACE FUNCTION anything_all_udf(
) RETURNS setof anything
AS $$
BEGIN
select * from anything;
END;
$$
LANGUAGE plpgsql;

Try (Note change of language):
CREATE OR REPLACE FUNCTION anything_all_udf(
) RETURNS setof anything
AS $$
select * from anything;
$$
LANGUAGE sql;

See here for how to do it using plpgsql:
http://www.postgresql.org/docs/8.4/interactive/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING

select * from anything_all_udf( );

Regards,
Iain

--
Adrian Klaver
aklaver@comcast.net

On Wednesday 06 January 2010 5:01:39 pm Iain Barnett wrote:

If I run the following (in either a terminal or the PgAdmin3 Query tool) I
get the error:

ERROR: query has no destination for result data
SQL state: 42601
Hint: If you want to discard the results of a SELECT, use PERFORM instead.
Context: PL/pgSQL function "anything_all_udf" line 3 at SQL statement

How am I'm supposed to call the function so that I get the same result as
the adhoc query it surrounds?

----

create table anything (
first serial primary key not null
, second varchar not null
, third timestamp default now() not null
);

insert into anything ( second ) values ( 'row a' );
insert into anything ( second ) values ( 'row b' );
insert into anything ( second ) values ( 'row c' );

CREATE OR REPLACE FUNCTION anything_all_udf(
) RETURNS setof anything
AS $$
BEGIN
select * from anything;
END;
$$
LANGUAGE plpgsql;

Try (Note change of language):
CREATE OR REPLACE FUNCTION anything_all_udf(
) RETURNS setof anything
AS $$
select * from anything;
$$
LANGUAGE sql;

See here for how to do it using plpgsql:
http://www.postgresql.org/docs/8.4/interactive/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING

select * from anything_all_udf( );

Regards,
Iain

--
Adrian Klaver
adrian.klaver@gmail.com