Postgresql – Pass an array or record to a function in PostgreSQL

arraycomposite-typesfunctionspostgresqlpostgresql-9.0

I have a task to pass arrays, records, and in some cases array of records as a parameter to functions in PostgreSQL.

Best Answer

Postgres has very flexible handling of arrays and composite types. This may be the sort of thing you are trying to do:

create type my_type as (val1 integer, val2 integer);

create function my_function(arr my_type[]) returns text language plpgsql as $$
begin
  return arr::text;
end;$$;

select my_function(array[row(1,2),row(3,4)]::my_type[]);

| my_function       |
| :---------------- |
| {"(1,2)","(3,4)"} |

dbfiddle here

Related Solutions

Postgresql – Pass a table array as a parameter to an upsert function in postgresql

I think yes. Haven't tried with actual INSERTs or UPDATEs yet, but this function works:

CREATE TABLE a (id integer);

CREATE OR REPLACE FUNCTION arraytest2(rec a[]) RETURNS SETOF integer AS
$body$
SELECT b.*
FROM unnest($1) b;
$body$
LANGUAGE sql;

This way you can write your INSERT statement as

INSERT INTO tbl_weightment 
SELECT (r).*
FROM unnest(rec) r;

And UPDATE as

UPDATE tbl_weightment 
SET 
    vname = r.vname,
    iquality = r.iquality,
    vhooks = r.vhooks,
    tstamp = r.tstamp
    vtare = r.vtare,
    vgross = r.vgross 
FROM unnest(rec) r
WHERE id=rec.id;

Postgresql – Why does PostgreSQL allow querying for array[0] even though it uses 1-based arrays

Postgres arrays are 1-based by default. And in typical applications it's best to stick with the default. But the syntax allows to start with any integer number. The documentation:

Subscripted assignment allows creation of arrays that do not use one-based subscripts. For example one might assign to myarray[-2:7] to create an array with subscript values from -2 to 7.

Examples:

SELECT ('[0:2]={1,2,3}'::int[])[0];    --> 1
SELECT ('[-7:-5]={1,2,3}'::int[])[-6]; --> 2

You can query any index of an array, if it does not exist you get NULL.

Normalize array subscripts for 1-dimensional array so they start with 1

Best Answer

Related Solutions

Postgresql – Pass a table array as a parameter to an upsert function in postgresql

Postgresql – Why does PostgreSQL allow querying for array[0] even though it uses 1-based arrays

Related Question