pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedaggregates
2+ messages / 2 participants
[nested] [flat]
* aggregates
@ 1998-08-03 03:19 Jade <thanh@ipinc.com>
0 siblings, 0 replies; 2+ messages in thread
From: Jade @ 1998-08-03 03:19 UTC (permalink / raw)
To: pgsql-sql@hub.org
Hi,
I was wonder if anyone has every done this before or if it is at all possible.
I have 3 tables formats,tree,items. The table creation are shown below. The values in
the table are also displayed.
CREATE TABLE formats (
type int4,
agregatename text
);
CREATE TABLE tree (
l1 char16,
l2 char16,
l3 char16,
l4 char16,
l5 char16,
ident int4,
type int4
);
CREATE TABLE items (
ident int4,
name text ,
address text,
email text,
url text,
title
);
INSERT INTO FORMATS VALUES(1,'name,email,url');
INSERT INTO TREE VALUES('level1','level2','level3',509,1);
INSERT INTO ITEMS VALUES(28,'foobar','1230 Xmarks Rd', 'foo@bar.com','http://www.perl.pl';);
I would like to get the results from ITEMS using returned results from FORMATS as ITEMS'
aggregate to prepare my select query.
I have tried to create a function:
CREATE FUNCTION displayformat(int4)
RETURNS text
AS 'select formats from formats where type= $1'
LANGUAGE 'sql';
and then do the below query:
SELECT displayformat(1) from ITEMS WHERE id=0028;
I get the below result:
displayformat
--------------
name,email,url
(1 row)
Welp! .. I wanted those results returned to me to be seen as aggregates that I would like to
select from ITEMS;
Is it possible to accomplish something like this? Maybe even in a different matter. I just thought
it would be good if I could have a way to set some sort of rule that states what aggregates I need
from the specified query in a table. Is this possible or do I need to somehow keep track of them
seperately (probably causing for the need of a separate query).
Thanks a lot.
-- Thanh
^ permalink raw reply [nested|flat] 2+ messages in thread
* Aggregates
@ 1999-07-11 20:43 qd@sea.com.ua
0 siblings, 0 replies; 2+ messages in thread
From: qd@sea.com.ua @ 1999-07-11 20:43 UTC (permalink / raw)
To: pgsql-sql
Hi, All !
PostgreSQL-6.4 lacks min/max functions for character types.
As I needed one I did the following:
create function max_str(bpchar, bpchar) returns bpchar as
'select $1 where bpchargt($1, $2) union select $2 where
bpcharge($2, $1)';
This function seems to work fine for 'char(20)'.
Next, I define the aggregate:
create aggregate smax (basetype=bpchar, sfunc1=max_str, stype1=bpchar,
initcond1='');
and I have a table with a column 'magic char(20)'
When I try to use my smax I get such an error:
select smax(magic) from my_table;
ERROR: bpcharin: the length of char() must be less than 4096.
I have no char type with such length. I checked if my
function max_str returns a correct length:
select length(max_str('aab' 'ab'));
It returns 2 as it should be.
So where is the bug ?
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~1999-07-11 20:43 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1998-08-03 03:19 aggregates Jade <thanh@ipinc.com>
1999-07-11 20:43 Aggregates qd@sea.com.ua
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox