Postgres Pro
Downloads
Home   >   mailing lists

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

From David G. Johnston
Subject Re: How to use row values as function parameters
Date
Msg-id CAKFQuwYcoZi6XBJfi5cmOWiPUxQGKvhLmVaxmMj_rhfZMYtHUQ@mail.gmail.com
Whole thread Raw
In response to How to use row values as function parameters  ("Andrus" <kobruleht2@hot.ee>)
Responses Re: How to use row values as function parameters  ("Andrus" <kobruleht2@hot.ee>)
List pgsql-general
Tree view
On Sat, May 14, 2016 at 4:47 PM, Andrus <kobruleht2@hot.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.


pgsql-general by date:

Previous
From: "Andrus"
Date:
Subject: How to use row values as function parameters
Next
From: Adrian Klaver
Date:
Subject: Re: How to use row values as function parameters