How to extract a node in a Postgres record datatype

Question

I am using Postgres 9.1 and I have a function that returns a record datatype using the row() function.

For a simple example, try:

select row(1,2,3);

It will return a single-cell row with "(1,2,3)" in it.

In my more complex function, it returns a geocode and other data as in:

(90,"Sydney, Australia",0.431951065304161,151.208784,-33.873982)

So what I am trying to do is figure out how to exact each of these node values in this "row" or "array" (whatever is technically correct).

Anybody that can help, this would be appreciated.

klin · Accepted Answer · 2015-11-01 22:34:32Z

1

Example function:

create or replace function a_function()
returns record language sql as $$
    select 90, 'Sydney, Australia'::text, 0.431951065304161, 151.208784, -33.873982;
$$;

select a_function();

                            a_function                            
------------------------------------------------------------------
 (90,"Sydney, Australia",0.431951065304161,151.208784,-33.873982)
(1 row)

You should call the function in from clause with a column definition list:

select *
from a_function() as (id int, city text, num1 numeric, num2 numeric, num3 numeric);

 id |       city        |       num1        |    num2    |    num3    
----+-------------------+-------------------+------------+------------
 90 | Sydney, Australia | 0.431951065304161 | 151.208784 | -33.873982
(1 row)

answered Nov 1, 2015 at 22:34

klin

123k15 gold badges240 silver badges262 bronze badges

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

Comments

Walker Farrow · Accepted Answer · 2015-11-01 22:25:30Z

0

I kept searching and I found the answer this doc:

http://www.tutorialspoint.com/postgresql/postgresql_data_types.htm

Look under "Accessing Composite Types".

Essentially the record array is an associative array (or key=>value pair).

You have to use the key name in order to pull it out of the array.

answered Nov 1, 2015 at 22:25

Walker Farrow

4,0289 gold badges36 silver badges58 bronze badges

Collectives™ on Stack Overflow

How to extract a node in a Postgres record datatype

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related