Re: [SQL] Howto get a group_number like row_number for groups
On Thu, Apr 8, 2010 at 2:51 PM, Andreas wrote: > or rank() over (order by object, ts) % 2 use dense_rank() instead -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Howto get a group_number like row_number for groups
Hi I'd like to have an alternating colorindex in the output of a query that consecutive rows that are the same within a colum the same number. The query generates a readable output from a log-table and a few others that hold referenced texts. log (log_id int, log_event_fk int, object_fk int , ts timestamp) events (event_id int, event text) objects (object_id int, object text, is_active bool) the easy part :) mind the the ordering is not primarily based on the timestamp select log_id, event, object, ts from log join events on event_id = log_event_fk join objects on object_id = object_fk where object.is_active order by object, ts Now I'd need a dynamically generated column that alternates between 0 and 1 so that I can later color the rows where object is the same. row_number() over (order by object, ts) % 2 or rank() over (order by object, ts) % 2 produces the 0/1 alternation for rows When I create a subselect for objects that adds the colorindex and join this to the log instead of objects, I get the group-color only if I omit the sorting on the timestamp. When I order the outer select by object, ts the colorindex gets 0 in every row. :( I'd like to get something as this 3, up, dev3, 2010-4-2 10:00, 0 8, down, dev3, 2010-4-2 14:00, 0 9, down, dev3, 2010-4-2 15:00, 0 1, up, dev7, 2010-4-2 09:00, 1 5, down, dev7, 2010-4-2 17:00, 1 2, up, dev11, 2010-4-2 12:00, 0 7, down, dev11, 2010-4-2 13:00, 0 . . regards :) -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Table Design for Hierarchical Data
The "parent" node in a genealogy is the mother-father tuple, so given that as a singularity it still fits a tree. On 04/08/2010 12:56 AM, Achilleas Mantzios wrote: > Στις Wednesday 07 April 2010 23:33:07 ο/η Yeb Havinga έγραψε: >> Achilleas Mantzios wrote: >>> Στις Wednesday 07 April 2010 11:06:44 ο/η Yeb Havinga έγραψε: >>> Achilleas Mantzios wrote: > You could also consider the genealogical approach, e.g. > > > The parents of any node to the root, i.e. the path of any node to the > root are depicted as > parents[0] : immediate parent > parents[1] : immediate parent of the above parent > > What I have more than one parent? >>> >>> Then it is no longer neither a tree, nor a hierarchical structure, but >>> rather a graph. >>> This a totally different problem. >>> >> My question was actually an attempt to point at the inability of what >> you call the 'genealogical approach' database design to store >> information of more than one parent. > > > Are you suggesting that we should change our definition of trees ADT, just > because it does not > fit the mere detail that humans have two parents? > Or are you just suggesting that the "genealogical" term is inaccurate? > > Take a look here: www.tetilab.com/roberto/pgsql/postgres-trees.pdf > >> >> regards, >> Yeb Havinga >> >> > > > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Problem with function returning a result set
Tom Lane, 08.04.2010 10:59: Thomas Kellerer writes: CREATE OR REPLACE FUNCTION get_employees(name_pattern varchar) RETURNS TABLE(id integer, full_name text) AS $$ BEGIN RETURN QUERY SELECT id, first_name||' '||last_name FROM employee WHERE last_name LIKE name_pattern ||'%'; END $$ LANGUAGE plpgsql; I get one row returned which is correct, but the ID column is null (but should be 1). Don't name the parameter the same as the table column ... regards, tom lane I knew it was something simple I overlooked ;) Thanks for the quick response. Regards Thomas -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Problem with function returning a result set
2010/4/8 Thomas Kellerer :
> Hi,
>
> I'm playing around with functions returning result sets, and I have a
> problem with the following function:
>
> -- Create sample data
> CREATE TABLE employee (id integer, first_name varchar(50), last_name
> varchar(50));
> INSERT INTO employee values (1, 'Arthur', 'Dent');
> INSERT INTO employee values (2, 'Zaphod', 'Beeblebrox');
> INSERT INTO employee values (3, 'Ford', 'Prefect');
> COMMIT;
>
> -- Create the function
> CREATE OR REPLACE FUNCTION get_employees(name_pattern varchar)
> RETURNS TABLE(id integer, full_name text)
> AS
> $$
> BEGIN
>
> RETURN QUERY
> SELECT id, first_name||' '||last_name
> FROM employee
> WHERE last_name LIKE name_pattern ||'%';
> END
> $$
> LANGUAGE plpgsql;
>
> COMMIT;
>
> Now when I run:
>
> SELECT *
> FROM get_employees('D');
>
> I get one row returned which is correct, but the ID column is null (but
> should be 1). It does not depend which row(s) I select through the
> procedure. I also tried to change the datatype of the returned id to int8
> and an explicit cast in the SELECT statement, but to no avail.
>
> When I define the function using SQL as a language (with the approriate
> changes), the ID column is returned correctly.
>
> I'm using Postgres 8.4.3 on Windows XP
> postgres=> select version();
> version
> -
> PostgreSQL 8.4.3, compiled by Visual C++ build 1400, 32-bit
> (1 row)
>
> What am I missing?
there are collision between SQL and PLpgSQL identifiers.
RETURNS TABLE(id integer, full_name text) AS
$$
BEGIN
RETURN QUERY
SELECT e.id, e.first_name||' '||e.last_name
FROM employee e
WHERE e.last_name LIKE e.name_pattern ||'%';
END
$$
LANGUAGE plpgsql;
use aliases.
Regards
Pavel Stehule
>
> Regards
> Thomas
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Problem with function returning a result set
Thomas Kellerer writes: > CREATE OR REPLACE FUNCTION get_employees(name_pattern varchar) > RETURNS TABLE(id integer, full_name text) > AS > $$ > BEGIN >RETURN QUERY > SELECT id, first_name||' '||last_name > FROM employee > WHERE last_name LIKE name_pattern ||'%'; > END > $$ > LANGUAGE plpgsql; > I get one row returned which is correct, but the ID column is null > (but should be 1). Don't name the parameter the same as the table column ... regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Problem with function returning a result set
Hi,
I'm playing around with functions returning result sets, and I have a problem
with the following function:
-- Create sample data
CREATE TABLE employee (id integer, first_name varchar(50), last_name
varchar(50));
INSERT INTO employee values (1, 'Arthur', 'Dent');
INSERT INTO employee values (2, 'Zaphod', 'Beeblebrox');
INSERT INTO employee values (3, 'Ford', 'Prefect');
COMMIT;
-- Create the function
CREATE OR REPLACE FUNCTION get_employees(name_pattern varchar)
RETURNS TABLE(id integer, full_name text)
AS
$$
BEGIN
RETURN QUERY
SELECT id, first_name||' '||last_name
FROM employee
WHERE last_name LIKE name_pattern ||'%';
END
$$
LANGUAGE plpgsql;
COMMIT;
Now when I run:
SELECT *
FROM get_employees('D');
I get one row returned which is correct, but the ID column is null (but should
be 1). It does not depend which row(s) I select through the procedure. I also
tried to change the datatype of the returned id to int8 and an explicit cast in
the SELECT statement, but to no avail.
When I define the function using SQL as a language (with the approriate
changes), the ID column is returned correctly.
I'm using Postgres 8.4.3 on Windows XP
postgres=> select version();
version
-
PostgreSQL 8.4.3, compiled by Visual C++ build 1400, 32-bit
(1 row)
What am I missing?
Regards
Thomas
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Table Design for Hierarchical Data
Achilleas Mantzios wrote: Στις Wednesday 07 April 2010 23:33:07 ο/η Yeb Havinga έγραψε: Achilleas Mantzios wrote: Στις Wednesday 07 April 2010 11:06:44 ο/η Yeb Havinga έγραψε: Achilleas Mantzios wrote: You could also consider the genealogical approach, e.g. The parents of any node to the root, i.e. the path of any node to the root are depicted as parents[0] : immediate parent parents[1] : immediate parent of the above parent What I have more than one parent? Then it is no longer neither a tree, nor a hierarchical structure, but rather a graph. This a totally different problem. My question was actually an attempt to point at the inability of what you call the 'genealogical approach' database design to store information of more than one parent. Are you suggesting that we should change our definition of trees ADT, just because it does not fit the mere detail that humans have two parents? Or are you just suggesting that the "genealogical" term is inaccurate? The latter, but rethinking it, why would genealogical be a bad word when applied to graph algorithm 'stuff' when words like parent, child, ancestor, sibling are common use. When I read 'genealogical' I had only the connotation 'family relations' in mind. I suspect that if looking at the definition of the word genealogy alone, it could very well include the study of single parent transitive relationships. However, not exclusively, so yes, IMHO something called the genealogical approach should not preclude polyhierarchies. regards Yeb Havinga -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Table Design for Hierarchical Data
Στις Wednesday 07 April 2010 23:33:07 ο/η Yeb Havinga έγραψε: > Achilleas Mantzios wrote: > > Στις Wednesday 07 April 2010 11:06:44 ο/η Yeb Havinga έγραψε: > > > >> Achilleas Mantzios wrote: > >> > >>> You could also consider the genealogical approach, e.g. > >>> > >>> > >>> The parents of any node to the root, i.e. the path of any node to the > >>> root are depicted as > >>> parents[0] : immediate parent > >>> parents[1] : immediate parent of the above parent > >>> > >>> > >> What I have more than one parent? > >> > > > > Then it is no longer neither a tree, nor a hierarchical structure, but > > rather a graph. > > This a totally different problem. > > > My question was actually an attempt to point at the inability of what > you call the 'genealogical approach' database design to store > information of more than one parent. Are you suggesting that we should change our definition of trees ADT, just because it does not fit the mere detail that humans have two parents? Or are you just suggesting that the "genealogical" term is inaccurate? Take a look here: www.tetilab.com/roberto/pgsql/postgres-trees.pdf > > regards, > Yeb Havinga > > -- Achilleas Mantzios -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
