Re: SETOF any table records AS return in pgSQL functions

On Wed, Sep 20, 2017 at 8:16 AM, athinivas <athinivas(at)gmail(dot)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.