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@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
