Re: How to use row values as function parameters - Mailing list pgsql-general

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