Record write access
According to the documentation for each()
, it returns setof(key text, value text)
. However, when using it with, SELECT
I cannot access key
or value
. I tried looking for information on types record
, but all I can find is the useless plpgsql related bit .
Let's say that I have the following table:
CREATE TABLE mytable (
id SERIAL NOT NULL PRIMARY KEY,
data HSTORE NOT NULL
);
And I run the query:
SELECT pair
FROM (
SELECT each(data) AS pair
FROM mytable
) AS pairs
I am getting lines like record
:
(key1,value1) (key2,value2) ...
If I try to access key
or value
, I get various errors.
-
It:
SELECT pair.key, pair.value FROM ( SELECT each(data) AS pair FROM mytable ) AS pairs
gives:
ERROR: missing FROM-clause entry for table "pair" LINE 1: pair.key, ^ ********** Error ********** ERROR: missing FROM-clause entry for table "pair" SQL state: 42P01 Character: 8
-
It:
SELECT pair.key, pair.value FROM ( SELECT each(data) AS pair (key, value) FROM mytable ) AS pairs
gives:
ERROR: syntax error at or near "(" LINE 4: each(attributes) AS pair (key, value) ^ ********** Error ********** ERROR: syntax error at or near "(" SQL state: 42601 Character: 71
-
It:
SELECT pairs.pair.key, pairs.pair.value FROM ( SELECT each(data) AS pair FROM mytable ) AS pairs
gives:
ERROR: schema "pairs" does not exist ********** Error ********** ERROR: schema "pairs" does not exist SQL state: 3F000
-
It:
SELECT pair[1], pair[2] FROM ( SELECT each(data) AS pair FROM mytable ) AS pairs
gives:
ERROR: cannot subscript type record because it is not an array ********** Error ********** ERROR: cannot subscript type record because it is not an array SQL state: 42804
How do I correctly access the members of a type record
in PostgreSQL?
source to share
In PostgreSQL 9.3, use LATERAL
.
regress=> SELECT p.key, p.value FROM mytable, LATERAL each(data) p;
key | value
-----+-------
a | b
c | d
(2 rows)
In older versions, you have to use the wildcard extension, which is a little ugly.
regress=> SELECT (pair).*
FROM (
SELECT each(data) AS pair
FROM mytable
) AS pairs
;
key | value
-----+-------
a | b
c | d
(2 rows)
Side note:
each
returns a typed result set with parameters OUT
so the return type and column names are known. It returns a set, i.e. It can return multiple lines for a single call:
regress=> \df each
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+------+------------------+-----------------------------------------+--------
public | each | SETOF record | hs hstore, OUT key text, OUT value text | normal
(1 row)
I mention this because a function that returns record
or has setof record
no typed parameters OUT
cannot be used in the way I showed above, you need to provide a special parameter list for such functions.
source to share