How to get the first field from an anonymous string type in PostgreSQL 9.4?

Question

=# select row(0, 1) ; row ------- (0,1) (1 row) 

How to get 0 inside the same request? I realized that this works, but is there an easy way?

 =# select json_agg(row(0, 1))->0->'f1' ; ?column? ---------- 0 (1 row) 

No luck with array type syntax [0] .

Thanks!

Answer 1

Your string type is anonymous, and therefore you cannot easily access its elements. What you can do is create a TYPE , and then drop your anonymous string to this type and access the elements defined in the type:

 CREATE TYPE my_row AS ( x integer, y integer ); SELECT (row(0,1)::my_row).x; 

As Craig Ringer said in his question, you should avoid creating anonymous strings to get you started, if you can help him, and enter all the data that you use in your data model and queries.

Answer 2

The json solution is very elegant. Just for fun, this solution using regexp (much uglier):

 WITH r AS (SELECT row('quotes, "commas", and a line break".',null,null,'"fourth,field"')::text AS r) --WITH r AS (SELECT row('',null,null,'')::text AS r) --WITH r AS (SELECT row(0,1)::text AS r) SELECT CASE WHEN rr ~ '^\("",' THEN '' WHEN rr ~ '^\("' THEN regexp_replace(regexp_replace(regexp_replace(right(rr, -2), '""', '\"', 'g'), '([^\\])",.*', '\1'), '\\"', '"', 'g') ELSE (regexp_matches(right(rr, -1), '^[^,]*'))[1] END FROM r 

When converting a string to text, PostgreSQL uses CSV formatting. I could not find any tools to import the cited CSV into an array, so the above is a crude text manipulation using mostly regular expressions. Maybe someone will find this useful!

Answer 3

If you just need the first element from any string, convert the string to JSON and select f1 ...

 SELECT row_to_json(row(0,1))->'f1' 

Or, if you always have two integers or a strict structure, you can create a temporary table (or type) and a function that selects the first column.

 CREATE TABLE tmptable(f1 int, f2 int); CREATE FUNCTION gettmpf1(tmptable) RETURNS int AS 'SELECT $1.f1' LANGUAGE SQL; SELECT gettmpf1(ROW(0,1)); 

Resources

https://www.postgresql.org/docs/9.2/static/functions-json.html https://www.postgresql.org/docs/9.2/static/sql-expressions.html