Postgres Pro
Downloads
Home   >   mailing lists

Re: [SQL] SETOF any table records AS return in pgSQL functions - Mailing list pgsql-sql

From David G. Johnston
Subject Re: [SQL] SETOF any table records AS return in pgSQL functions
Date
Msg-id CAKFQuwZSKy+EHz3ZMkDTxC9awZgH=+huD48-djjkHgPqzKfB0w@mail.gmail.com
Whole thread Raw
In response to [SQL] SETOF any table records AS return in pgSQL functions  (athinivas <athinivas@gmail.com>)
List pgsql-sql
Tree view
On Wed, Sep 20, 2017 at 8:16 AM, athinivas <athinivas@gmail.com> wrote:
hi,

Could anyone say pgSQL equivalent of SELECT * FROM any_table inside stored
procedure in MySQL ??

PS: I'm able to understand only functions are there in pgSQL. But couldn't
get a way for SETOF polymorphic columns inside functions.

​Went looking for a doc example but couldn't quickly find one...

In short you have two choices in PostgreSQL:
1. Define the return record structure in CREATE FUNCTION​
2. Define the return record structure in the FROM clause <FROM func_call (col1 text, col2 int)>

To write a function that supports #2 you do:

CREATE FUNCTION ... RETURNS SETOF record

This is documented at least at:

https://www.postgresql.org/docs/9.6/static/sql-select.html

FROM Clause -> function_name (4th paragraph)

The syntax blob includes a "column_definition" placeholder though there is no format definition on that page - only the "followed by a column definition list in the form ( column_name data_type [, ... ])" fragment buried within this section.

David J.

pgsql-sql by date:

Previous
From: athinivas
Date:
Subject: [SQL] SETOF any table records AS return in pgSQL functions
Next
From: Ertan Küçükoğlu
Date:
Subject: [SQL] Single SQL to find if any record exists across several tables