Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
table_type_example
-- https://github.com/postgres/postgres/blob/master/src/test/regress/sql/typed_table.sql
postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# create function myfunc(anyarray) returns void as $$
test$# begin
test$# insert into persons select * from unnest($1);
test$# end; $$ language plpgsql;
CREATE FUNCTION
test=# CREATE TYPE person_type AS (id int, name text); -- table type
CREATE TYPE
test=# CREATE TABLE IF NOT EXISTS persons OF person_type;
CREATE TABLE
test=# select cast(x as person_type) from (values (1, 'a'), (2, 'b')) x;
x
-------
(1,a)
(2,b)
(2 rows)
test=# truncate table persons;
TRUNCATE TABLE
test=# do $$
declare foo person_type[];
begin
foo := (select array(select cast(x as person_type) from (values (1, 'a'), (2, 'b')) x));
perform myfunc(foo);
end;
$$ language plpgsql;
DO
test=# table persons;
id | name
----+------
1 | a
2 | b
(2 rows)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment