Search Postgresql Archives

Re: How to use row values as function parameters

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

 



On Sat, May 14, 2016 at 5:34 PM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote:
On 05/14/2016 02:13 PM, Andrus wrote:
Hi!

Thank you.


Use a CTE and move the function call to the select list - then explode
the result in the main query.
Basically:
WITH func_cte AS (
SELECT func_call(tbl)
FROM tbl
)​

​>SELECT (func_call).*
FROM func_cte;
​>​The parens are required to make the parser see func_call as a column
name instead of a table name.​

I tried in 9.5

CREATE or replace FUNCTION crtKAIVE(
_doktyybid text default 'GVY'
)
RETURNS TABLE (
id integer
)
AS $f_crkaive$
select 1
$f_crkaive$ LANGUAGE sql STABLE;

create temp  table ko ( doktyyp text ) on commit drop;
insert into ko values ('G');


WITH func_cte AS (
SELECT crtKAIVE(ko.doktyyp)
FROM ko
)​
​SELECT (crtKAIVE).*
FROM func_cte;

but got strange error


ERROR:  syntax error at or near "​"
LINE 18: )​

How to fix ?

I am guessing you did the same thing I did, copy and pasted David's example and modified. Seems there are some 'hidden' characters present. Re-entering the code from scratch got this:

test=# WITH func_cte AS (
SELECT crtKAIVE(ko.doktyyp)
FROM ko
)SELECT (crtKAIVE).* from func_cte
;
ERROR:  type integer is not composite


​There must be some kind of implicit conversion being done here.  Since the function is defined as returning a single column the resultant column is non-composite and thus doesn't accept the ".*" construct.  If the function were to return multiple columns would need to use the CTE to avoid multiple evaluation during the ".*" expansion.  With a single column it doesn't matter.

But if you are going to use 9.5 the original query should just work - <FROM tbl, func(tbl)> is equivalent to <FROM tbl LATERAL func(tbl)>​ (going from memory...) and regardless the lateral form can be made to work in 9.5 whatever the syntax.

David J.



[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