James Cloos wrote:
I have a table which includes a text column containing posix-style
paths. Ie, matching the regexp "^[^/]+(/[^/]+)*$".
I need to do a query of a number of columns from that table, plus the
count of rows which are "children" of the current row.
The query:
SELECT count(*) AS nch FROM m WHERE o = (SELECT o FROM m WHERE id=30016)
AND name ILIKE (SELECT name || '/%' FROM m WHERE id=30016);
selects that extra column given the id.
A view containing all of the columns from m plus a column matching the
above select would cover my needs well.
But I haven't been able to get the syntax right.
It would help if you provided:
a) statements to create sample data
b) expected results from sample data
Does this do what you want?
select * from m, (SELECT count(*) AS nch FROM m WHERE o = (SELECT o FROM
m WHERE id=30016) AND name ILIKE (SELECT name || '/%' FROM m WHERE
id=30016)) om;
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
