Stored functions postgresql returning table

Question

A novice when it comes to stored procedures/functions. I have searched Google, Stackoverflow, and Youtube and are finding all sorts of examples that are convoluted, some not in English.

I'm trying to understand the basic syntax for a stored function to return a table in Postgresql. In MySql this is elementary but I can't seem to wrap my head around the syntax for Postgresql I have the SQL statement I need to return the rows I want (table), as seen below. I have tried the following code but it doesn't work. Help is much appreciated, thanks in advance.

CREATE OR REPLACE FUNCTION Getcurrent()
RETURNS table AS $schedule$

$BODY$ BEGIN

SELECT *
FROM archived_table
WHERE table_id>=ALL(SELECT table_id FROM archived_table);

RETURN schedule;
END;$BODY$
   LANGUAGE plpgsql;

********** Error **********

ERROR:  syntax error at or near "AS"
LINE 2: RETURNS table AS $schedule$
                      ^

This is the error message.

I have referenced the following link and have had no luck with this.https://www.postgresql.org/docs/9.1/static/sql-createfunction.html Im using pgAdminIII, in the public schema, on my company's server.

The desired results is to have the table returned once the function is called.

returns table requires the columns list as shown in the manual link you posted. — Clodoaldo Neto
– Clodoaldo Neto, Commented Apr 13, 2017 at 15:46
Unrelated, but: if you intend to return the row with the highest table_id there are much more efficient ways of doing that in Postgres. — user330315
– user330315, Commented Apr 13, 2017 at 16:07
Also: "In MySql this is elementary" no it's not because you can't return a result set from a function there. — user330315
– user330315, Commented Apr 13, 2017 at 16:08

pozs · Accepted Answer · 2017-04-13 16:03:38Z

1

RETURNS TABLE is not complete, hence the error message.

You can use the RETURNS SETOF <table_name> form, if you intend to return all columns of a table.

Otherwise, you'll need to mention every output column by name and type, with either RETURNS TABLE:

RETURNS TABLE (
   col_alias_1 INT,
   col_alias_2 TEXT,
   col_alias_3 <some_other_type>,
   ...
)

Or with OUT parameters + RETURNS SETOF RECORD to indicate that you'll (possibly) return multiple rows at once.

Also, if your operation is as simple as a few SQL statements, use LANGUAGE SQL instead:

CREATE OR REPLACE FUNCTION Getcurrent()
  RETURNS SETOF archived_table
  LANGUAGE SQL
AS $BODY$
  SELECT *
  FROM archived_table
  WHERE table_id>=ALL(SELECT table_id FROM archived_table);
$BODY$;

answered Apr 13, 2017 at 16:03

pozs

36.6k5 gold badges61 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Bolaji Over a year ago

Pozs your second box worked like a charm, thanks. I'm in the midst of learning more about stored procedures and I'm not really sure how to implement the "returns setof record"

Collectives™ on Stack Overflow

Stored functions postgresql returning table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related