How can I use:
select * from <some_table>;
in a FUNCTION in Postgres?
-
You mean returning from a function? Otherwise the question doesn't make any sense. You then should specify what you tried and what error you got.Frans Bouma– Frans Bouma2009-04-12 12:18:17 +00:00Commented Apr 12, 2009 at 12:18
-
You might want to provide more information what you want to achieve, what did you tried, how did fail, and how you'd want it to work.user80168– user801682009-04-13 19:23:26 +00:00Commented Apr 13, 2009 at 19:23
Add a comment
|
2 Answers
CREATE OR REPLACE FUNCTION my_function() RETURNS INTEGER AS '
DECLARE
your_record your_table%ROWTYPE;
BEGIN
FOR your_record IN SELECT * FROM your_table
LOOP
--
-- You can access fields of your table using .
-- your_record.your_field
...
END LOOP;
END;
' LANGUAGE 'plpgsql'
STABLE;
or
CREATE OR REPLACE FUNCTION my_function() RETURNS INTEGER AS '
DECLARE
your_record your_table%ROWTYPE;
BEGIN
SELECT * INTO your_record FROM your_table;
--
-- You can access fields of your table using .
-- your_record.your_field
END;
' LANGUAGE 'plpgsql'
STABLE;
EDIT:
With join returning a record:
CREATE OR REPLACE FUNCTION my_function() RETURNS SETOF record AS '
DECLARE
your_record record;
BEGIN
--
-- You should specify a list of fields instead of *
--
FOR your_record IN SELECT * FROM your_table INNER JOIN ...
RETURN NEXT your_record;
END LOOP;
END;
' LANGUAGE 'plpgsql'
STABLE;
To use my_function(), you have to specify fields and datatypes: See details here
2 Comments
Markus
But in case I want to make a join with other table?
There is a smipler method, if you want to use SQL in the function, use SQL language in the function:
CREATE FUNCTION getallzipcodes()
RETURNS SETOF zip AS
$BODY$
SELECT * FROM zip;
$BODY$
LANGUAGE 'sql';
And this might be useful too (how to call the function)
SELECT function_returning_setof(); -- Wrong!
SELECT * FROM function_returning_setof(); -- OK!
SELECT function_returning_scalar(); -- OK
