Обсуждение: How to call SETOF function?
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;
select * from anything_all_udf( );
Regards,
Iain
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
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