Returning a row from a function with an appended array field

On Nov 9, 2011, at 20:19, Wes Cravens <wcravens@cortex-it.com> wrote:

I have an adjacency list kind of table

CREATE TABLE thingy (
id int,
parent int
);

I'd like to be able to write a procedural function that returns a row or
rows from this table with an appended field that represents the children.

Something like this pseudo code:

FOR row IN SELECT * FROM thingy
LOOP
RETURN NEXT row,[SELECT id FROM thingy WHERE parent_id = id]
END LOOP,
RETURN

Any help much appreciated,

Wes

Use "WITH RECURSIVE" instead of a function.

David J.

On 11/9/2011 7:34 PM, David Johnston wrote:

On Nov 9, 2011, at 20:19, Wes Cravens <wcravens@cortex-it.com> wrote:

I have an adjacency list kind of table

CREATE TABLE thingy (
id int,
parent int
);

I'd like to be able to write a procedural function that returns a row or
rows from this table with an appended field that represents the children.

Something like this pseudo code:

FOR row IN SELECT * FROM thingy
LOOP
RETURN NEXT row,[SELECT id FROM thingy WHERE parent_id = id]
END LOOP,
RETURN

Any help much appreciated,

Wes

Use "WITH RECURSIVE" instead of a function.

I apologize but I don't know how that would work. An example would help.

Also... my pseudo code above was a little flawed:
RETURN NEXT row,[SELECT id FROM thingy WHERE parent_id = id]
should be...
RETURN NEXT row,[SELECT id FROM thingy WHERE parent_id = row.id]

On 10 November 2011 02:54, Wes Cravens <wcravens@cortex-it.com> wrote:

On 11/9/2011 7:34 PM, David Johnston wrote:

Use "WITH RECURSIVE" instead of a function.

I apologize but I don't know how that would work.  An example would help.

There are fine examples in the documentation for the SELECT statement.

--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.

On 11/9/2011 7:19 PM, Wes Cravens wrote:

I have an adjacency list kind of table

CREATE TABLE thingy (
id int,
parent int
);

I'd like to be able to write a procedural function that returns a row or
rows from this table with an appended field that represents the children.

Just in case someone else want's an answer to this tread... and despite
the fact that the postgresql documentation is excellent and has plenty
of examples, WITH RECURSIVE is still a bad solution...

I already needed an independent get_children function:

CREATE OR REPLACE
FUNCTION get_children (
lookup_id INT
) RETURNS
int[] AS
$$
SELECT array_agg( id )
FROM (
SELECT id
FROM thingy
WHERE parent_id = $1
ORDER BY id
) t;
$$ LANGUAGE
'sql';

And I just used that in a view to get what I wanted:

CREATE OR REPLACE VIEW thingy_view AS
SELECT *,get_children(id) AS children FROM thingy;

I then updated all of my other get_ accessor postgresql functions to use
the view instead of the base table.

FTW

Wes

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Wes Cravens
Sent: Thursday, November 10, 2011 11:54 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Returning a row from a function with an appended
array field

On 11/9/2011 7:19 PM, Wes Cravens wrote:

I have an adjacency list kind of table

CREATE TABLE thingy (
id int,
parent int
);

I'd like to be able to write a procedural function that returns a row
or rows from this table with an appended field that represents the

children.

Just in case someone else want's an answer to this tread... and despite the
fact that the postgresql documentation is excellent and has plenty of
examples, WITH RECURSIVE is still a bad solution...

I already needed an independent get_children function:

CREATE OR REPLACE FUNCTION
get_children (
lookup_id INT
) RETURNS
int[] AS
$$
SELECT array_agg( id )
FROM (
SELECT id
FROM thingy
WHERE parent_id = $1
ORDER BY id
) t;
$$ LANGUAGE
'sql';

And I just used that in a view to get what I wanted:

CREATE OR REPLACE VIEW thingy_view AS
SELECT *,get_children(id) AS children FROM thingy;

I then updated all of my other get_ accessor postgresql functions to use the
view instead of the base table.

FTW

Wes

--------------------------------------------------

If you only care about one level of hierarchy then, yes, WITH RECURSIVE is
overkill. You want to use WITH RECURSIVE in those situations where the
depth of the hierarchy is unknown.

David J.

On 11/10/2011 12:05 PM, David Johnston wrote:

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Wes Cravens
Sent: Thursday, November 10, 2011 11:54 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Returning a row from a function with an appended
array field

On 11/9/2011 7:19 PM, Wes Cravens wrote:

I have an adjacency list kind of table

CREATE TABLE thingy (
id int,
parent int
);

I'd like to be able to write a procedural function that returns a row
or rows from this table with an appended field that represents the

children.

If you only care about one level of hierarchy then, yes, WITH RECURSIVE is
overkill. You want to use WITH RECURSIVE in those situations where the
depth of the hierarchy is unknown.

Yes agreed... WITH RECURSIVE would be handy for something like
get_ancestors or get_descendents.

Wes

On 10 Nov 2011, at 19:51, Wes Cravens wrote:

On 11/10/2011 12:05 PM, David Johnston wrote:

On 11/9/2011 7:19 PM, Wes Cravens wrote:

I have an adjacency list kind of table

CREATE TABLE thingy (
id int,
parent int
);

I'd like to be able to write a procedural function that returns a row
or rows from this table with an appended field that represents the children.

If you only care about one level of hierarchy then, yes, WITH RECURSIVE is
overkill. You want to use WITH RECURSIVE in those situations where the
depth of the hierarchy is unknown.

Yes agreed... WITH RECURSIVE would be handy for something like
get_ancestors or get_descendents.

If you only need one level of recursion, you can just use a self-join.

SELECT parent.id AS parent_id, child.id as child_id
FROM thingy AS parent
LEFT OUTER JOIN thingy AS child ON (child.parent_id = parent.id)

Alban Hertroys

--
The scale of a problem often equals the size of an ego.