pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
aggregates
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