Re: [SQL] SETOF any table records AS return in pgSQL functions

Поиск
Список
Период
Сортировка
От David G. Johnston
Тема Re: [SQL] SETOF any table records AS return in pgSQL functions
Дата
Msg-id CAKFQuwZSKy+EHz3ZMkDTxC9awZgH=+huD48-djjkHgPqzKfB0w@mail.gmail.com
обсуждение исходный текст
Ответ на [SQL] SETOF any table records AS return in pgSQL functions  (athinivas <athinivas@gmail.com>)
Список pgsql-sql
Дерево обсуждения
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 по дате отправления:

Предыдущее
От: athinivas
Дата:
Сообщение: [SQL] SETOF any table records AS return in pgSQL functions
Следующее
От: Ertan Küçükoğlu
Дата:
Сообщение: [SQL] Single SQL to find if any record exists across several tables