CODEWITHSUNDEEP
sqlpostgresqlfunctionstored-procedurespostgresql-9.1
Bolaji
Bolaji

Reputation: 21

Stored functions postgresql returning table

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.

Upvotes: 2

Views: 5747

Answers (1)

pozs
pozs

Reputation: 36274

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$;

Upvotes: 1

Related Questions