CODEWITHSUNDEEP
postgresqlescaping
JoshuaBoshi
JoshuaBoshi

Reputation: 1286

postgresql array / composite output

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].

Upvotes: 2

Views: 378

Answers (2)

Craig Ringer
Craig Ringer

Reputation: 324771

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.

Upvotes: 1

mys
mys

Reputation: 2473

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.

Upvotes: 2

Related Questions