Re: How to use row values as function parameters

On Sat, May 14, 2016 at 4:47 PM, Andrus <kobruleht2(at)hot(dot)ee> wrote:

> Table ko should used to pass parameters to crtKAIVE() function.
> ko has always single row.
>
> I tried
>
> CREATE or replace FUNCTION public.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');
> select * from ko, crtkaive(ko.doktyyp)
>
> but got error
>
> ERROR: function expression in FROM cannot refer to other relations of
> same query level
>
> How to fix this so that ko can used to pass parameters to crtkaive ?
>
> Posted also in
>
>
> http://stackoverflow.com/questions/37231624/how-to-use-table-row-values-as-function-parameters

​Upgrade your version and make use of the LATERAL feature.

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.​

David J.