How to declare an array of rowtype in a PostgreSQL function?

Question

I am trying to create a PostgreSQL function where I will loop over the rows of a query and store some of them in an array, before doing more stuff with it. How can I create an array of rowtype?

CREATE OR REPLACE FUNCTION forExample() RETURNS integer AS
$BODY$
DECLARE
    r "WEBHOST"%rowtype;
    b "WEBHOST"%rowtype[];   <- This does not work !!!
BEGIN
    FOR r IN SELECT * FROM "WEBHOST"
    LOOP
        array_append(b, r);
    END LOOP;
    RETURN 33;
END
$BODY$
LANGUAGE 'plpgsql';

The above function will be more complex, but I am providing a simplified version for this question.

Erwin Brandstetter · Accepted Answer · 2023-09-13 14:47:15Z

22

CREATE OR REPLACE FUNCTION for_example()
  RETURNS integer
  LANGUAGE plpgsql AS
$func$
DECLARE
   r "WEBHOST";          -- optionally schema-qualify table name: public."WEBHOST"  
   b "WEBHOST"[];        -- this works
BEGIN
   FOR r IN 
      SELECT * FROM "WEBHOST"
   LOOP
      b := b || r;       -- this, too
   END LOOP;

   RAISE NOTICE '%', b;  -- get feedback
   RETURN 33;
END
$func$;

%rowtype is generally not necessary. Normally, the associated rowtype of a table is available as type of the same name.

And do not quote the language name.

You have to assign or discard (with PERFORM instead of SELECT) the results of a function calls in PL/pgSQL.

It's also not a good idea to use CaMeL-case identifiers in Postgres. Use legal, lower-case identifiers to make your life easier.

The best for last: much simpler with the aggregate function array_agg():

CREATE OR REPLACE FUNCTION for_example()
  RETURNS integer
  LANGUAGE plpgsql AS
$func$
DECLARE
   b "WEBHOST"[];
BEGIN
   SELECT array_agg(tbl) INTO b FROM "WEBHOST" tbl;       

   RAISE NOTICE '%', b;
   RETURN 33;
END
$func$;

edited Sep 13, 2023 at 14:47

answered Mar 10, 2014 at 17:22

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

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

2 Comments

Denis Stephanov Over a year ago

Hi, it's little older but I need define variable where I can insert data, next delete it and insert new and so on ... I wanted use your solution but postgres give me compilation error type "my_schema.my_tbl[]" does not exist. Can you please help me?

Erwin Brandstetter Over a year ago

@DenisStephanov: Not sure I understand. Anyway, please post it as new question. Comments are not the place. You can always link here for context or add a comment here to link back.

Collectives™ on Stack Overflow

How to declare an array of rowtype in a PostgreSQL function?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related