How to call table function from another function in PostgreSQL?

Question

I am new to PostgreSQL.

I have a function

CREATE OR REPLACE FUNCTION func1 ( a_person_id integer) RETURNS TABLE (granted_group_id varchar(10)) AS $body$

          WITH v_granted_group_list AS (
          SELECT DISTINCT a.group_id 
                FROM a_table a
               )
        SELECT v.group_id FROM v_granted_group_list v;

$body$
LANGUAGE sql;

I want to use the output of this in other function func2.

I tried to do that as in the following code and got an error.

What can we use in place of the v_access_groups array?

CREATE OR REPLACE FUNCTION func2 ( a_person_id IN integer,mhrc_emp_no IN varchar(50)) 
 RETURNS TABLE (granted_group_id varchar(10)) AS $body$
DECLARE

   v_access_groups varchar[];
BEGIN

      v_access_groups := func1(a_person_id);
      ---- this gives error
      ---ERROR:  malformed array literal: "LCCHG"
      ----DETAIL:  Array value must start with "{" or dimension information.
      --------CONTEXT:  PL/pgSQL function func2(integer,character varying) line 14 at SQL statement

      -- what can we you in place of v_access_groups array
    RETURN v_access_groups;
  END;
$body$
LANGUAGE PLPGSQL;

I want to use the result of func2 in yet another function in a select query

CREATE OR REPLACE FUNCTION func3 ( a_blurb_id integer, a_person_id integer,mhrc_emp_no varchar(20)) RETURNS boolean AS $body$
DECLARE

     v_acc_count         numeric;
     v_accessAllowed     boolean:=FALSE;
BEGIN
    SELECT COUNT(*) INTO v_acc_count
    FROM table1 agfa
    where  agfa.group_id IN (
        SELECT TO_CHAR(column_value) AS group_id
        FROM TABLE(func2(a_person_id,mhrc_emp_no))
    );
    RETURN TRUE;
  END;
$body$
LANGUAGE PLPGSQL;

How can I achieve this in PostgreSQL?

use lateral join

murison
– murison

2017-08-02 08:03:45 +00:00
Commented Aug 2, 2017 at 8:03 — murison
– murison, Commented Aug 2, 2017 at 8:03
@mursion i am new to postgres . How can be achieve this ?

Himanshu sharma
– Himanshu sharma

2017-08-02 08:06:21 +00:00
Commented Aug 2, 2017 at 8:06 — Himanshu sharma
– Himanshu sharma, Commented Aug 2, 2017 at 8:06

Laurenz Albe · Accepted Answer · 2017-08-02 09:33:34Z

1

You can use a set returning function (table function) in a query in the same way as you use a table.

So to assign the results to a text[] named v_access_groups, you could proceed as follows:

SELECT array_agg(granted_group_id) INTO v_access_groups FROM func1(a_person_id);

But your func2 should simply contain:

RETURN QUERY SELECT * FROM func1(a_person_id);

Your func3 should work if you omit the TABLE(...) expression – just treat the function like a table with no additional syntax.

answered Aug 2, 2017 at 9:33

Laurenz Albe

257k22 gold badges311 silver badges387 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Vao Tsun · Accepted Answer · 2017-08-02 09:00:43Z

you can reference function returning table just as usual table. Eg you function func1 returns a column varchar(10). Not having your structure I'll use generate_series. so the lateral join @murison is latking about would be smth like:

t=# select pg_class.oid 
from pg_class
join lateral generate_series(11700,11711,1) g on pg_class.oid = g 
;
  oid  
-------
 11701
 11704
 11707
 11711
(4 rows)

here I get oids between 11700 and 11711, because my generate_series will return values: 11700, 11701, 11702 and so on till 11711...

your desired solution with IN operator is quite simple as well:

t=# select oid from pg_class
where oid IN (select generate_series(11700,11711,1));
  oid  
-------
 11701
 11704
 11707
 11711
(4 rows)

so your dividing the result of func1 to array in func2 is just not needed.

Collectives™ on Stack Overflow

How to call table function from another function in PostgreSQL?

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related