CODEWITHSUNDEEP
sqlpostgresqluser-defined-types
Dinesh
Dinesh

Reputation: 4559

Syntax for user defined composite type literal

I have some UDTs (user defined types) in my code (PostgreSQL 9.2)

create type pairs_t as (keyname varchar, e_value varchar);
create type values_t as (e_values varchar[]);
create type allvalues_t as (regions values_t, products pairs_t);

and used as in:

create or replace function foo( IN _x allvalues_t[] ) returns void as $$ begin...

The actual UDTs in application are more complex.

But I cannot figure out how to type up a test case. E.g., if I wanted (a,prod-a),(b,prod-b) for products and () for regions, how would a SELECT * from foo(...) statement in pgAdmin SQL window look like? What should ... be?

I would appreciate if someone could post a guide or a page that describes this syntax. I have looked at postgresql man pages but no luck.

Upvotes: 0

Views: 429

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 659247

Check out CREATE TYPE in the manual.

Your example defines the type allvalues_t, but later uses allvalues. Also values_t -> value_t. Looks like a simple typos. You can't be that sloppy if you want to get it right.

Syntax of for composite could be type:

SELECT * from foo(('{"(\"(\"\"{arr_a,arr_b}\"\")\",\"(foo,bar)\")","(\"(\"\"{arr_a,arr_b}\"\")\",\"(foo,bar)\")"}'))

How can you find out yourself?

CREATE TEMP TABLE pairs_t (keyname varchar, e_value varchar);
-- OR CREATE TYPE for a more permanent solution. 
INSERT INTO pairs_t VALUES ('foo', 'bar');

CREATE TEMP TABLE values_t (e_values varchar[]);
INSERT INTO values_t VALUES ('{arr_a, arr_b}');


CREATE TEMP TABLE allvalues_t (regions values_t, products pairs_t);
INSERT INTO allvalues_t VALUES((SELECT x FROM values_t x), (SELECT x FROM pairs_t x));

CREATE TEMP TABLE test (t allvalues_t[]);
INSERT INTO test VALUES (ARRAY[(SELECT x FROM allvalues_t x), (SELECT x FROM allvalues_t x)]);

SELECT * FROM test
SELECT x FROM allvalues_t x;

Upvotes: 1

Related Questions