How to pass a table or rows to a function in Postgresql?

Question

Here is the code, the current type of argument is array, but I want to pass a table or rows instead.

create or replace function skyband_sortedlist(rest point[]) 
    returns setof point
as $$ 
declare

    last_x integer :=0;
    last_y integer :=0;
begin
    for ipoint in (select s0.x,s0.y from unnest(rest))
    loop 
        if ipoint.x>last_x and ipoint.y<>last_y then
            last_x = ipoint.x;
            last_y = ipoint.y;
            return next;
        end if;
    end loop;
end;
$$ language plpgsql;

Laurenz Albe · Accepted Answer · 2017-05-15 12:47:14Z

18

A row is represented by a composite type, like

CREATE TYPE mytype  AS (
   id integer,
   name text,
   fromdate timestamp with time zone
);

You can use such a type as function argument.

For each PostgreSQL table, there automatically exists a type with the same name and columns:

CREATE TABLE mytable (
   id integer PRIMARY KEY,
   name text,
   fromdate timestamp with time zone NOT NULL
);

So you can create a function that takes an array of this type as argument:

CREATE OR REPLACE FUNCTION myfunc(arg mytable[]) RETURNS void
   LANGUAGE plpgsql IMMUTABLE STRICT AS
$$DECLARE
   t mytable;
BEGIN
   FOREACH t IN ARRAY arg LOOP
      RAISE NOTICE 'id = %', t.id;
   END LOOP;
END;$$;

You can call it like this (assuming that there are two rows in mytable):

SELECT myfunc(array_agg(mytable)) FROM mytable;
NOTICE:  id = 1
NOTICE:  id = 2
┌────────┐
│ myfunc │
├────────┤
│        │
└────────┘
(1 row)

Alternatively, you can create a function that takes a cursor as argument:

CREATE OR REPLACE FUNCTION myfunc(arg refcursor) RETURNS void
   LANGUAGE plpgsql IMMUTABLE STRICT AS
$$DECLARE
   t mytable;
BEGIN
   LOOP
      FETCH NEXT FROM arg INTO t;
      EXIT WHEN NOT FOUND;
      RAISE NOTICE 'id = %', t.id;
   END LOOP;
END;$$;

This can be called in a transaction as follows:

BEGIN;
DECLARE c CURSOR FOR SELECT * FROM mytable;
SELECT myfunc('c');

NOTICE:  id = 1
NOTICE:  id = 2
┌────────┐
│ myfunc │
├────────┤
│        │
└────────┘
(1 row)

COMMIT;

answered May 15, 2017 at 12:47

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

2 Comments

Chen Over a year ago

Thanks, by the way, will the name of columns be saved after being converted into array? If not, how to access specific column?

Laurenz Albe Over a year ago

The array is an array of mytable, so each of the entries has the attributes of mytable. You could reference them like this: (arg[2]).name.

Andomar · Accepted Answer · 2017-05-15 14:25:45Z

8

You can use the ARRAY_AGG constructor to convert a rowset to an array. For example, the following expression results in an array of all rows from table t1:

(SELECT ARRAY_AGG(t1) FROM t1)

Full example:

CREATE TABLE t1 (id INT, name TEXT);
INSERT INTO t1 values (1, 'Joe'), (2, 'Arnold');

CREATE OR REPLACE FUNCTION public.f1(arg t1[])
 RETURNS SETOF t1
 LANGUAGE sql
AS $$
SELECT * FROM UNNEST(arg);
$$;

SELECT f1((SELECT ARRAY_AGG(t1) FROM t1));

edited May 15, 2017 at 14:25

answered May 15, 2017 at 12:55

Andomar

239k55 gold badges387 silver badges412 bronze badges

Collectives™ on Stack Overflow

How to pass a table or rows to a function in Postgresql?

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related