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

Groups > comp.databases.postgresql > #587 > unrolled thread

Returning a table in SQL

Back to article view | Back to comp.databases.postgresql

Contents

  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

#587 — Returning a table in SQL

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 ! 


---
This email is free from viruses and malware because avast! Antivirus protection is active.
http://www.avast.com


--- news://freenews.netfront.net/ - complaints: news@netfront.net ---

[toc] | [next] | [standalone]

#588

AP wrote on 26.08.2014 19:39:
> 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 !
>

You need to use the function like a table if it returns a table:

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

[toc] | [prev] | [next] | [standalone]

#589

On 26.08.2014 21:10, Harry Tuttle wrote:

> You need to use the function like a table if it returns a table:
>
> with report as (
>      select *
>      fromsome_report('Customer','2014-08-01','2014-08-08')
> )
> select *
> from report

I guess there is a space missing between "from" and "some_report".

It seems a simpler variant would be

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

Or does that not work?  (I do have no PG available for testing right now.)

Kind regards

	robert

[toc] | [prev] | [next] | [standalone]

#590

Robert Klemme wrote on 26.08.2014 22:28:
>> with report as (
>>      select *
>>      fromsome_report('Customer','2014-08-01','2014-08-08')
>> )
>> select *
>> from report
>
> I guess there is a space missing between "from" and "some_report".

Yes, of course. Copy & Paste error.

  
> It seems a simpler variant would be
>
> select *
> from some_report('Customer','2014-08-01','2014-08-08')
>
> Or does that not work?  (I do have no PG available for testing right now.)

Yes that would work just as well.
I assumed the example was a stripped down example of a large query that would benefit from the CTE.

Btw: if you don't have Postgres available you can use http://sqlfiddle.com

[toc] | [prev] | [next] | [standalone]

#591

On 26.08.2014 23:34, Harry Tuttle wrote:
> Robert Klemme wrote on 26.08.2014 22:28:

>> It seems a simpler variant would be
>>
>> select *
>> from some_report('Customer','2014-08-01','2014-08-08')
>>
>> Or does that not work?  (I do have no PG available for testing right
>> now.)
>
> Yes that would work just as well.
> I assumed the example was a stripped down example of a large query that
> would benefit from the CTE.

Yeah, that could be.

> Btw: if you don't have Postgres available you can use http://sqlfiddle.com

Cool!  Thanks for the hint!  Afterwards I remembered that I frequently 
used something like this for test data generation:

select 'foo ' || generate_series as x
from generate_series(1, 10)

so it should work. :-)

Kind regards

	robert

[toc] | [prev] | [next] | [standalone]

#593

Actually, I figured it out :

with report as (select some_report('Customer','2014-08-01','2014-08-08') cr)
  select
    (cr).field1,
    (cr).field2,
    (cr).field3
  from report;

gives three separated columns.

WITH clause is for clarity solely

"Harry Tuttle"  wrote in message news:c644cpF2g32U1@mid.individual.net...

AP wrote on 26.08.2014 19:39:
> 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 !
>

You need to use the function like a table if it returns a table:

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


---
This email is free from viruses and malware because avast! Antivirus protection is active.
http://www.avast.com


--- news://freenews.netfront.net/ - complaints: news@netfront.net ---

[toc] | [prev] | [next] | [standalone]

#596

AP wrote on 27.08.2014 22:37:
> Actually, I figured it out :
>
> with report as (select some_report('Customer','2014-08-01','2014-08-08') cr)
>   select
>     (cr).field1,
>     (cr).field2,
>     (cr).field3
>   from report;
>
> gives three separated columns.
>
> WITH clause is for clarity solely
>

It's still "wrong".

You should really put the function into the from clause.

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

[toc] | [prev] | [next] | [standalone]

#597

On 28.08.2014 17:51, Harry Tuttle wrote:
> AP wrote on 27.08.2014 22:37:
>> Actually, I figured it out :
>>
>> with report as (select
>> some_report('Customer','2014-08-01','2014-08-08') cr)
>>   select
>>     (cr).field1,
>>     (cr).field2,
>>     (cr).field3
>>   from report;
>>
>> gives three separated columns.
>>
>> WITH clause is for clarity solely

No, in PostgreSQL it is an optimization fence that actually influences 
optimizer's behavior.  Apart from that I don't see the improvement in 
clarity caused by the additional layer.

> It's still "wrong".
>
> You should really put the function into the from clause.

s/should/must/

> select *
> from some_report('Customer','2014-08-01','2014-08-08')

Kind regards

	robert

[toc] | [prev] | [next] | [standalone]

#600

On 2014-08-28, Harry Tuttle <SOZRBLNTLEEE@spammotel.com> wrote:
> AP wrote on 27.08.2014 22:37:
>> Actually, I figured it out :
>>
>> with report as (select some_report('Customer','2014-08-01','2014-08-08') cr)
>>   select
>>     (cr).field1,
>>     (cr).field2,
>>     (cr).field3
>>   from report;
>>
>> gives three separated columns.
>>
>> WITH clause is for clarity solely
>>
>
> It's still "wrong".
>
> You should really put the function into the from clause.
>
> select *
> from some_report('Customer','2014-08-01','2014-08-08')

both produce the same result.


never do this:

  select (some_report('Customer','2014-08-01','2014-08-08')).* ;

eg: try it with this create_report. 

create or replace function some_report(text,text,text) 
returns table ( field1 int, field2 int, field3 int )
language SQL as $$
 with a as ( select generate_series (1,4) as r order by random())
 select a.r,a.r,a.r from a $$;



-- 
umop apisdn


--- news://freenews.netfront.net/ - complaints: news@netfront.net ---

[toc] | [prev] | [next] | [standalone]

#601

select * from some_report() gives me one column containing a record. select 
(cr).field1 , (cr1).field2, (cr1).field3 from some_report() cr1 gives 3 
columns - as needed.

Since we are on the subject, is there a downside to such "parametrized 
views" ?

"Harry Tuttle"  wrote in message news:c691fnFahj8U1@mid.individual.net...

AP wrote on 27.08.2014 22:37:
> Actually, I figured it out :
>
> with report as (select some_report('Customer','2014-08-01','2014-08-08') 
> cr)
>   select
>     (cr).field1,
>     (cr).field2,
>     (cr).field3
>   from report;
>
> gives three separated columns.
>
> WITH clause is for clarity solely
>

It's still "wrong".

You should really put the function into the from clause.

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


---
This email is free from viruses and malware because avast! Antivirus protection is active.
http://www.avast.com


--- news://freenews.netfront.net/ - complaints: news@netfront.net ---

[toc] | [prev] | [next] | [standalone]

#602

On 2014-09-01, AP <unixpronospam@verizon.net> wrote:
> select * from some_report() gives me one column containing a record. select 
> (cr).field1 , (cr1).field2, (cr1).field3 from some_report() cr1 gives 3 
> columns - as needed.
>
> Since we are on the subject, is there a downside to such "parametrized 
> views" ?

no optimisation for the where clause, or any index for an order by.

-- 
umop apisdn


--- news://freenews.netfront.net/ - complaints: news@netfront.net ---

[toc] | [prev] | [next] | [standalone]

#603

That a good, if obvious, point. Obvious because all indexing and 
optimization would need to happen within whatever query is wrapped into this 
parametrized view. That said, is there a way to build a useful index on a 
record column ? On a record-returning function column ?

And do all queries get same shot at optimization whether they come from 
within a Plpgsql block or a cursor, or from within a SQL function or view ?

"Jasen Betts"  wrote in message 
news:lu1jc2$1j5$1@gonzo.reversiblemaps.ath.cx...

On 2014-09-01, AP <unixpronospam@verizon.net> wrote:
> select * from some_report() gives me one column containing a record. 
> select
> (cr).field1 , (cr1).field2, (cr1).field3 from some_report() cr1 gives 3
> columns - as needed.
>
> Since we are on the subject, is there a downside to such "parametrized
> views" ?

no optimisation for the where clause, or any index for an order by.

-- 
umop apisdn


--- news://freenews.netfront.net/ - complaints: news@netfront.net --- 


---
This email is free from viruses and malware because avast! Antivirus protection is active.
http://www.avast.com


--- news://freenews.netfront.net/ - complaints: news@netfront.net ---

[toc] | [prev] | [next] | [standalone]

#604

On 2014-09-04, AP <unixpronospam@verizon.net> wrote:
> That a good, if obvious, point. Obvious because all indexing and 
> optimization would need to happen within whatever query is wrapped into this 
> parametrized view. That said, is there a way to build a useful index on a 
> record column ? On a record-returning function column ?
>
> And do all queries get same shot at optimization whether they come from 
> within a Plpgsql block or a cursor, or from within a SQL function or view ?

if you write the function to contain the where clause it can sometimes be as
efficient,

plpgsql functions don't get query time optimsation which saves time
but may cost efficiency. this may or may not help. evaluate can be
used to force optimisation.


views are bascially as efficient as writing it out in full.

-- 
umop apisdn


--- news://freenews.netfront.net/ - complaints: news@netfront.net ---

[toc] | [prev] | [next] | [standalone]

#592

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

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.postgresql

csiph-web