Search Postgresql Archives

Re: Letting a function return multiple columns instead of a single complex one

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



>
> The reason that I need this is because of my other question (is there in pg
> a function like oracle's rownum?). The function get_a_foo looks in reality a
> bit more like the next:
>
> create type foo_extended as (a int, b int, rowno int);
>
> create or replace function get_a_foo_func(int)
>  returns setof foo_extended as
> '
> declare
> tmp_row foo_extended%rowtype;
> i int;
> begin
> i := 1;
>
> for tmp_row in (select * from foo where a = $1) loop
>  tmp_row.rowno := i;
>  return next tmp_row;
>  i := i + 1;
> end loop;
>
> end;
> '
> language plpgsql volatile;
>
> create or replace function get_a_foo(int)
>  returns setof foo_extended as
> '
> select * from get_a_foo_func($1);
> '
> language sql volatile;
>
> The function get_a_foo_func runs a query and adds to each row of the result
> a rownum like number. The other, wrapper, function is to make it possible to
> give a set as an imput parameter: unfortunately this is something that
> doesn't seem to be supported by pl/pgsql.
>
> Yours,
>
> Aarjan Langereis
>
>

Maybe you can do something like:


 create type foo_extended as (a int, b int, rowno int);

create or replace function get_a_foo(int) returns setof foo_extended as '
 create temp sequence seq1;
 select *, nextval('seq1') from foo where a = $1;
' language sql volatile;


--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux