Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.postgresql > #592

Re: Returning a table in SQL

Show all headers | View raw

On 08/26/2014 07:39 PM, AP wrote:
> Dear USENET,
> 
> If I have a function that returns a table , such as
> 
> create function some_report(in param1 text) returns table(rec1 integer, rec2 
> text) as
> $body$
>     select blablabla from table1;
> $body$ language 'sql'
> 
> with report as (select some_report('Customer','2014-08-01','2014-08-08') )
> select * from report
> 
> still returns me a table with one column into which all record fields are 
> packed.
> 
> How do I access rec1 and rec2 individually in such a case ?
> 
> Thank you all in advance ! 


I dont know postgres that well, but your function takes one argument,
and you call a function with three arguments. Could it be that you have
another function that returns one column? I wrote a small test:

create table table1 (a int, b text);
insert into table1 (a,b) values (1,'x'),(2,'y');

CREATE FUNCTION some_report(text,date,date)
RETURNS TABLE(f1 int, f2 text)
    AS $$ SELECT a, b from table1 $$
    LANGUAGE SQL;


with report as (select some_report('Customer','2014-08-01','2014-08-08') )
select * from report

{ "Value": "(1,x)", "Type": "record" }
{ "Value": "(2,y)", "Type": "record" }


Tested on postgres 9.3.1

Back to comp.databases.postgresql | Previous | Next — Previous in thread | Find similar

Thread

Returning a table in SQL "AP" <unixpro-nospam@verizon.spam.net> - 2014-08-26 13:39 -0400
  Re: Returning a table in SQL Harry Tuttle <SOZRBLNTLEEE@spammotel.com> - 2014-08-26 21:10 +0200
    Re: Returning a table in SQL Robert Klemme <shortcutter@googlemail.com> - 2014-08-26 22:28 +0200
      Re: Returning a table in SQL Harry Tuttle <SOZRBLNTLEEE@spammotel.com> - 2014-08-26 23:34 +0200
        Re: Returning a table in SQL Robert Klemme <shortcutter@googlemail.com> - 2014-08-27 08:36 +0200
    Re: Returning a table in SQL "AP" <unixpronospam@verizon.net> - 2014-08-27 16:37 -0400
      Re: Returning a table in SQL Harry Tuttle <SOZRBLNTLEEE@spammotel.com> - 2014-08-28 17:51 +0200
        Re: Returning a table in SQL Robert Klemme <shortcutter@googlemail.com> - 2014-08-28 21:45 +0200
        Re: Returning a table in SQL Jasen Betts <jasen@xnet.co.nz> - 2014-08-30 05:45 +0000
        Re: Returning a table in SQL "AP" <unixpronospam@verizon.net> - 2014-09-01 03:02 -0400
          Re: Returning a table in SQL Jasen Betts <jasen@xnet.co.nz> - 2014-09-01 10:56 +0000
            Re: Returning a table in SQL "AP" <unixpronospam@verizon.net> - 2014-09-04 02:57 -0400
              Re: Returning a table in SQL Jasen Betts <jasen@xnet.co.nz> - 2014-09-04 11:21 +0000
  Re: Returning a table in SQL Lennart Jonsson <erik.lennart.jonsson@gmail.com> - 2014-08-27 19:33 +0200

csiph-web