CODEWITHSUNDEEP
databasepostgresqlplpgsqlpostgresql-9.4
Himanshu sharma
Himanshu sharma

Reputation: 7891

How to call table function from another function in PostgreSQL?

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?

Upvotes: 2

Views: 5471

Answers (2)

Laurenz Albe
Laurenz Albe

Reputation: 246092

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.

Upvotes: 1

Vao Tsun
Vao Tsun

Reputation: 51406

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.

Upvotes: 1

Related Questions