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
Doing the below worked:
test=# WITH func_cte AS (
SELECT crtKAIVE(ko.doktyyp)
FROM ko
)SELECT * from func_cte
;
crtkaive
----------
1
(1 row)
test=# WITH func_cte AS (
SELECT crtKAIVE(ko.doktyyp)
FROM ko
)SELECT crtKAIVE from func_cte
;
crtkaive
----------
1
(1 row)
>
> Andrus.
>
--
Adrian Klaver
adrian.klaver@aklaver.com