postgresql array / composite output

Question

In my app, I am using postgresql to return composite type values like this:

select row('a','b','c','d');

This query returns:

(a,b,c,d)

I am using more complex types, which contains arrays of other composite types etc. In my DB layer, I parse returned value and "unpack" arrays and other composites from it.

The problem?

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

and

select row('{1,2,3,4}');

gives exactly same output:

("{1,2,3,4}")

How can I distinguish when this '{1,2,3,4}' is meant as regular string (possibly supplied by user) and when it is meant as array?

Should not be there something like escape sequence for { and } in composite field value?

P.S. I cannot use something like select * from row[1,2,3].

mys · Accepted Answer · 2012-11-18 04:00:26Z

2

The safest way to determine column type is to query result set metadata.

I can't tell you how because you didn't specify your framework/language. In general you need to extend some result set class and resolve this disambiguity there.

edited Nov 18, 2012 at 4:00

answered Nov 18, 2012 at 3:12

mys

2,49319 silver badges23 bronze badges

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

1 Comment

JoshuaBoshi Over a year ago

I knew about possiblity of queriing metadata of result set, but you (together with Craig Ringer) helped me to look on a problem from different view. Thank you both.

Craig Ringer · Accepted Answer · 2012-11-18 09:13:51Z

1

Since you're using anonymous records, this will be challenging. Observe:

SELECT pg_typeof( row('{1,2,3,4}') ), pg_typeof( row(array[1,2,3,4]) );

If possible use named types with CREATE TYPE instead of anonymous records (ROW() constructors) . Then you can examine the rowtypes.

CREATE TYPE r1 AS (col1 text);
CREATE TYPE r2 AS (col1 integer[]);

SELECT pg_typeof( row('{1,2,3,4}')::r1 ), pg_typeof( row(array[1,2,3,4])::r2 );

There may be more detail in the protocol-level result set metadata, as Ondrej suggests, offering ways to do this without having to use record types.

edited Nov 18, 2012 at 9:13

answered Nov 18, 2012 at 3:50

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

2 Comments

mvp Over a year ago

your SQL gives exact same result for both: record. What gives?

Craig Ringer Over a year ago

@mvp Exactly - you can't just use pg_typeof to determine the internal type of a record. I'll amend my answer to make that clearer.

Collectives™ on Stack Overflow

postgresql array / composite output

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related