pgplsql function for a SELECT statement

[davidchardonnet]

Hello,

I want to make a plpgsql function to launch a huge select statement like

select f1,f2,....f75 from table where id=parameter

the request must get only one record from the database.
I tried many syntaxes, but i cant find something that works...

Does someone knows how to do this?

Thank you

David

 

[ceco]

this can be achieved with standard sql function too, see

http://www.postgresql.org/docs/8.0/interactive/sql-select.html

there is example
CREATE FUNCTION distributors(int) RETURNS SETOF distributors AS $$
SELECT * FROM distributors WHERE did = $1;
$$ LANGUAGE SQL;

with plpgsql here I found an example in the user comments

http://www.postgresql.org/docs/8.0/interactive/plpgsql-declarations.html

CREATE OR REPLACE FUNCTION f_test() RETURNS SETOF tbl_mytest AS $$
DECLARE
myrec record;
BEGIN
FOR myrec IN SELECT id, foo FROM tbl_mytest LOOP
RETURN NEXT myrec;
END LOOP;
RETURN;
END;
$$ LANGUAGE 'plpgsql';

there are other places in the documentarion for this thing, but I can't find them now, hope it is enough though