Function returning subset of columns from table (return type)

Поиск
Список
Период
Сортировка
От Myk
Тема Function returning subset of columns from table (return type)
Дата
Msg-id 20080203195700.fabcb01d.myk@waxfrenzy.org
обсуждение исходный текст
Ответы Re: Function returning subset of columns from table (return type)
Список pgsql-general
Hi

I'm pretty new to PostgreSQL, and have encountered a bit of trouble with functions, namely the return type. Version is
8.0.15.

I have the following table:

note (
    id int,
    added date,
    updated date,
    text varchar(1000)
)

and want to define a function that just returns the dates and text by id. I initially just did:

create function note_get (id int)
returns setof note
as 'select * from note where id=$1' language sql;

which was fine. Then later I thought I'd try formatting the columns (they're only intended for display):

create function note_get (
    id int
)
returns setof record
as '
select
    to_char (added, ''Mon D YYYY''),
    to_char (updated, ''Mon D YYYY''),
    text
from
    note
where
    id=$1
' language sql;

but this gives me
ERROR:  a column definition list is required for functions returning "record"

Further reading led me to:

create function note_get (
    id int,
    out added varchar(12),
    out updated varchar(12),
    out text varchar(1000)
)
returns setof record
...

which got me
ERROR:  CREATE FUNCTION / OUT parameters are not implemented

at which point I thought it best to go out for a walk :)

How do I create a function that returns a number of columns like this?

Thanks
--
Mike

В списке pgsql-general по дате отправления:

Предыдущее
От: Ron Mayer
Дата:
Сообщение: Re: [pgsql-advocacy] PostgreSQL Certification
Следующее
От: "dfx"
Дата:
Сообщение: R: how to add array of objects to a record