On Sat, May 14, 2016 at 5:34 PM, Adrian Klaver <adrian.kla...@aklaver.com>
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.
