[SQL] casting to arrays

2003-07-18 Thread Mike Rylander 
I have a rather odd table structure that I would like to simplify to be a view 
(for some definition of simplify).  The current idea I have is to shovel 
values from multiple rows in one table into an array in the view.  The tables 
look something like this:

create table person (
  id  serial,
  name  varchar
);

create table stuff (
  person_id references person (id) on delete restrict,
  stuff_name varchar
);

The view would go something like:

create view person_with_stuff as
  select p.id as id,
   p.name as name,
   ( select s.stuff_name
 from stuff
 where s.person_id = p.id
   )::varchar[] from person p;

Is anything like this possible?  I know this may not be good form, but 
unfortunately (or perhaps fortunately, since it means I have a job) there are 
business reasons for this, supporting old apps and such.

Thanks in advance!
-- 
Mike Rylander

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [SQL] casting to arrays

2003-07-18 Thread Mike Rylander 

Thank you!  This is great news.  Is there a projected release date for 7.4?  
Also, is there a published roadmap, or should I just get on the developers 
list?

Thanks again.
---
Mike Rylander

On Friday 18 July 2003 05:34 pm, Joe Conway wrote:
> Mike Rylander wrote:
> > I have a rather odd table structure that I would like to simplify to be a
> > view (for some definition of simplify).  The current idea I have is to
> > shovel values from multiple rows in one table into an array in the view. 
> > The tables look something like this:
>
> 
>
> > Is anything like this possible?  I know this may not be good form, but
> > unfortunately (or perhaps fortunately, since it means I have a job) there
> > are business reasons for this, supporting old apps and such.
>
> Not possible in current releases, but it will be in 7.4 (about to start
> beta). It looks like this:
>
> create table person (id  integer, name  varchar);
> insert into person values(1,'Bob');
> insert into person values(2,'Sue');
>
> create table stuff (person_id integer, stuff_name text);
> insert into stuff values(1,'chair');
> insert into stuff values(1,'couch');
> insert into stuff values(1,'lamp');
> insert into stuff values(2,'table');
> insert into stuff values(2,'shirt');
>
> create or replace view person_with_stuff as select p.id as id, p.name as
> name, ARRAY(select s.stuff_name from stuff s where s.person_id = p.id)
> as stuff from person p;
>
> regression=# select * from person_with_stuff;
>   id | name |   stuff
> +--+
>1 | Bob  | {chair,couch,lamp}
>2 | Sue  | {table,shirt}
> (2 rows)
>
> HTH,
>
> Joe


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] converting interval to timestamp

2003-08-01 Thread Mike Rylander 
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1


If the number of seconds between timestamps is acceptable then this is the 
solution I use:

SELECT ABS(EXTRACT(EPOCH FROM t.field1) - EXTRACT(EPOCH FROM t.field2)) AS 
diff FROM table AS t;

Adjust to your column and WHERE needs.  One caveat:  this only works for dates 
within the UNIX epoch ( after 1969-12-31).

On Thursday 31 July 2003 12:05 pm, teknokrat wrote:
> The difference of two dates/timestamps always gives an interval. is
> there a way to convert this interval into  number such as number of
> milliseconds or number of days?
>
> Also does anyone know what field type an interval would map to in jdbc?
>
> thanks
>
>
>
> ---(end of broadcast)---
> TIP 9: the planner will ignore your desire to choose an index scan if your
>   joining column's datatypes do not match

- -- 
Mike Rylander
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.0.7 (GNU/Linux)

iD8DBQE/KmAOgRDV1fFA+3cRAgbVAJ9B03Pxsn+N+Xg2C/a4gw3j28KSsgCeNA7+
y2rYedgRdTY/BiNSfVJTvOs=
=kVkm
-END PGP SIGNATURE-


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [SQL] new max function

2003-10-17 Thread Mike Rylander 

Here's mine:

CREATE FUNCTION max2 (INTEGER,INTEGER) RETURNS INTEGER
  LANGUAGE SQL AS
  'SELECT CASE WHEN $1 > $2 THEN $1 ELSE $2 END';

This returns:

database=# select max2(1,2);
 max2 
--
2
(1 row)

database=# select max2(3,1);
 max2 
--
3
(1 row)


On Friday 17 October 2003 02:13 pm, Rodrigo Gesswein wrote:
> Hello!
>
>I'm looking for a function to calculate the max value from two numbers,
> something like max2(a,b) with a,b int
>
>Does anyone have the trick ?
>
>Thank you in advance..
>
> Rodrigo!
>
> ---(end of broadcast)---
> TIP 3: if posting/reading through Usenet, please send an appropriate
>   subscribe-nomail command to [EMAIL PROTECTED] so that your
>   message can get through to the mailing list cleanly

-- 
Mike Rylander


---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [SQL] How do i extract a certain bit from a bigint column

2004-05-12 Thread Mike Rylander 
On Wednesday 12 May 2004 07:05 am, Mats Sjöberg wrote:
> Hello everyone
> In a table i have a column status of type bigint.
> I need to create a view of the table including all rows with bit 4 set
> (value 8).
> At the same time i need to exclude excludig all rows with bit 2 set.
>
> What is the syntax to extract those bits?
> I have tested get_bit(string, offset) but this requires a string, and not a
> bigint.
> Is there a function to

The easiest way is to test for a bit using bitwise and:

SELECT * FROM table WHERE (status & 2::BIGINT) <> 0 AND (status & 8::BIGINT) = 
0;

-miker

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [SQL] [HACKERS] MERGE-esque Functionallity (was: Adding MERGE to the TODO list (resend with subject))

2004-05-14 Thread Mike Rylander 
On Tuesday 11 May 2004 09:44 am, Bruce Momjian wrote:
[snip]
> > > > Bruce Momjian kirjutas E, 10.05.2004 kell 06:58:
> > > > > Added to TODO:
> > > > >
> > > > >   * Add MERGE command that does UPDATE, or on failure, INSERT
> > > >
[snip]

Hello all.

I have been lurking here for a bit and the MERGE topic (on [HACKERS]) caught 
my eye, so I had a go at implementing a basic version of MERGE-on-INSERT in 
pl/pgsql.  It is attached below, and any comments are welcome.  I find it 
useful on "status" type tables, though it is not very nice when there are 
many clients (table locking to avoid race conditions).

Hope someone will find it useful!

-miker









-- 
-- Merge on INSERT functionallity for Postgres 7.3+
-- 
-- [EMAIL PROTECTED] / 5-14-04
-- 
-- CAVEAT EMPTOR: Uses table locks to avoid concurrency issues,
-- 		  so it WILL slow down heavily loaded tables.
-- 		  This effecivly puts the table into
-- 		  TRANSACTION ISOLATION LEVEL SERIALIZABLE mode.
-- 

CREATE OR REPLACE FUNCTION add_merge_on_insert (
	TEXT,  -- table name
	TEXT,  -- key column
	TEXT[] -- column list to update on deduplication
 ) RETURNS TEXT
   RETURNS NULL ON NULL INPUT
   SECURITY INVOKER
   LANGUAGE 'plpgsql'
   AS '

DECLARE
	tablename	ALIAS FOR $1;
	keycol		ALIAS FOR $2;
	updatecols	ALIAS FOR $3;
	trig		TEXT;
	arraydims	TEXT;

BEGIN
	trig := \'
		CREATE FUNCTION "\' || tablename || \'_merge_on_insert_proc" () RETURNS TRIGGER AS \'\'
		DECLARE
			orig \' || quote_ident(tablename) || \'%ROWTYPE;
		BEGIN
			LOCK TABLE \' || quote_ident(tablename) || \' IN ROW EXCLUSIVE MODE;

			SELECT INTO orig * FROM  \' || quote_ident(tablename) || \' WHERE \' || quote_ident(keycol)|| \' = NEW.\' || quote_ident(keycol) || \';

			IF NOT FOUND THEN
RETURN NEW;
			END IF;

			UPDATE \' || quote_ident(tablename) || \' SET \'; 

	arraydims := array_dims(updatecols);
	FOR i IN 1 .. (substring(arraydims from (position(\':\' in arraydims) + 1 ) for ( position(\']\' in arraydims) - (position(\':\' in arraydims) + 1 ) )))::INT LOOP
		trig := trig || quote_ident(updatecols[i]) || \' = COALESCE( NEW.\' || quote_ident(updatecols[i]) || \', orig.\' || quote_ident(updatecols[i]) || \'), \';
	END LOOP;

	trig := substring( trig from 0 for (character_length(trig) - 1));

	trig := trig || \' WHERE \' || quote_ident(keycol)|| \' = NEW.\' || quote_ident(keycol) || \';
			RETURN NULL;
		END;
		\'\' LANGUAGE \'\'plpgsql\'\';
	\';

	EXECUTE trig;
	EXECUTE \'
		CREATE TRIGGER "\' || tablename || \'_merge_on_insert_trig" BEFORE INSERT
			ON \' || quote_ident(tablename) || \' FOR EACH ROW
			EXECUTE PROCEDURE "\' || tablename || \'_merge_on_insert_proc" ();
	\';

	RETURN \'FUNCTION \' || tablename || \'_merge_on_insert_proc (); TRIGGER \' || tablename || \'_merge_on_insert_trig;\';
END;

';

CREATE OR REPLACE FUNCTION remove_merge_on_insert (
	TEXT  -- table name
 ) RETURNS TEXT
   RETURNS NULL ON NULL INPUT
   SECURITY INVOKER
   LANGUAGE 'plpgsql'
   AS '

BEGIN
	EXECUTE \'DROP FUNCTION "\' || $1 || \'_merge_on_insert_proc" () CASCADE;\';
	RETURN \'FUNCTION \' || $1 || \'_merge_on_insert_proc (); TRIGGER \' || $1 || \'_merge_on_insert_trig;\';
END;

';


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [SQL] Trigger problem

2004-06-09 Thread Mike Rylander 
kasper wrote:

> Hi guys
> 
> Im tryint to make a trigger that marks a tuble as changed whenever someone
> has updated it
> 
> my table looks something like this
> 
> create table myTable (
> ...
> changed boolean;
> )
> 
> now ive been working on a trigger and a sp that looks like this, but it
> doesnt work...
> 
> create function myFunction returns trigger as '
> begin
> new.changed = true;

The line above is using the SQL equaliy opperator, you want the assignment
operator:

  :=

as in 

new.changed := true;

> return new;
> end;
> ' language 'plpgsql';
> 
> create trigger myTrigger
> after update on lektioner
> for each row
> execute procedure myFunction();
> 
> 
> the code compiles, runs, and doesnt whine about anything, but nothing
> changes...
> 
> any ideas??
> 
> - Kasper

-miker


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [SQL] How to filter on timestamps?

2004-07-09 Thread Mike Rylander 


B.W.H. van Beest wrote:

> 
> 
> I have a table where one of the columns is of type 'TIMESTAMP'
> 
> How can I do a query to filter on the TIMESTAMP value, e.g. to obtain
> all rows earlier than a certain time stamp?

Think of the math opperators '<' and '>' as 'before' and 'after',
respectively.  ie:

SELECT * FROM table WHERE begin_date > '2004-07-06';

You can also use BETWEEN:

SELECT * FROM table WHERE update_timestamp BETWEEN '2004-07-01' AND
'2004-07-06';

Remember that when timestamps are cast to dates, they are cast with
00:00:00.0 as the time part.

See the docs on this at
http://www.postgresql.org/docs/7.4/interactive/datatype-datetime.html and
http://www.postgresql.org/docs/7.4/interactive/functions-datetime.html .

--miker
> 
> Regards,


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] implementing an out-of-transaction trigger

2004-09-15 Thread Mike Rylander 
>I've come across a situation where I'd like to use some kind of
"out-of-transaction
>trigger" to do some processing after changes to some tables, but
without extending
>the duration of the main transaction. Of course, it's important that
the processing be
>completed so it has to be, as far as possible, reliable and "safe". The extra
>processing should be completed within a reasonable time after the original
>transaction, but it needn't happen immediately. 
  
Check out
  http://www.postgresql.org/docs/7.4/static/sql-listen.html
and
  http://www.postgresql.org/docs/7.4/static/sql-notify.html

Then look at the Rules system for generating a NOTIFY:
   http://www.postgresql.org/docs/7.4/static/sql-createrule.html

--miker

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [SQL] sql

2004-10-25 Thread Mike Rylander 
On Mon, 25 Oct 2004 10:13:37 +0200, cristivoinicaru
<[EMAIL PROTECTED]> wrote:
> I have a postgres table like this:
> 
> CREATE TABLE "temp50" (
>  "gc" character varying(36),
>  "co" character varying(7),
>  "data" date,
>  "ora" smallint
> 
> );
> 
> It contains the following records:
> 
> 5003707G9G9419 22012BB 10-14-2004 82
> 5003707G9G9419 22012BC 10-14-2004 44
> 5003707G9G9419 22022BB 10-14-2004 82
> 5003707G9G9420 22022BC 10-18-2004 49
> 
> I'd  like the result of the sql interogation to be like this:
> 
> 5003707G9G9419 22012BB 10-14-2004 82
> 5003707G9G9420 22022BC 10-18-2004 49
> 
> Explanations:
> I want like sql interogation to select only one record from each "gc" group
> record (who has the same code "gc" (that means that the "gc" field will be
> unique key)) with the following two condition:
> 1)from each "gc" group of records to select the record who has the value of
> "ora" field maxim.
> 2)if two ore more record have the same value of the maxim value of the "ora"
> to select oly one of them

Try:

SELECT * FROM temp50 GROUP BY gc ORDER BY ora DESC;


--miker

> 
> Thanks!
> 
> ---(end of broadcast)---
> TIP 8: explain analyze is your friend
>

---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [SQL] sql

2004-10-25 Thread Mike Rylander 
On Mon, 25 Oct 2004 05:44:06 -0600, Andrew J. Kopciuch
<[EMAIL PROTECTED]> wrote:
> On Monday 25 October 2004 05:20, Mike Rylander wrote:
> > SELECT * FROM temp50 GROUP BY gc ORDER BY ora DESC;
> 
> You can not have have expressions (columns etc.) in the SELECT list that are
> either not in a GROUP BY clause, or used in an aggregate function when you
> use GROUP BY in the statement.  By saying SELECT *, means you would have to
> GROUP BY gc, co, data, ora ...

Doh!  Of course.   Should have had coffee before writing that ;)

> 
> That isn't going to do what he wants.  And the SQL you stated should give you
> an error:
> 
> ERROR:  column "temp50.co" must appear in the GROUP BY clause or be used in an
> aggregate function
> 
> What he wants to do is use DISTINCT ON:
> 
> SELECT DISTINCT ON (gc) gc, co, data, ora FROM temp50 ORDER BY gc, ora DESC;
> 
> Andy
>

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] tree structure photo gallery date quiery

2004-11-16 Thread Mike Rylander 
 p1.lft between p2.lft and p2.rgt and p1.id = 7;
>  id | parent |name
> ++
>   1 |  0 | Root
>   3 |  1 | Middleton
>   7 |  3 | From The Footplate
> (3 rows)
> 
> -- Select parent and subordinates - also want to convert to view
> nymr=# select p1.*, g.* from tree as p1, tree as p2, gallery g where
> g.id = p1.id and p1.lft between p2.lft and p2.rgt and p2.id = 1;
>  id | lft | rgt | id | parent |name
> +-+-+++
>   1 |   1 |  14 |  1 |  0 | Root
>   2 |   2 |   9 |  2 |  1 | NYMR
>   3 |  10 |  13 |  3 |  1 | Middleton
>   4 |   3 |   4 |  4 |  2 | Steam Gala
>   5 |   5 |   6 |  5 |  2 | Diesel Gala
>   6 |   7 |   8 |  6 |  2 | From The Footplate
>   7 |  11 |  12 |  7 |  3 | From The Footplate
> (7 rows)
> 
> -- use the one above to select photos - another view
> nymr=# select count(pid), max(added) from photos where id in (
> nymr(# select p1.id from tree as p1, tree as p2 where p1.lft between
> p2.lft and p2.rgt and p2.id = 1
> nymr(# );
>  count |  max
> ---+
>  4 | 2004-11-10 12:12:00+00
> (1 row)
> 
> nymr=# select count(pid), max(added) from photos where id in (
> nymr(# select p1.id from tree as p1, tree as p2 where p1.lft between
> p2.lft and p2.rgt and p2.id = 2
> nymr(# );
>  count |  max
> ---+
>  3 | 2004-11-10 12:12:00+00
> (1 row)
> 
> nymr=# select count(pid), max(added) from photos where id in (
> nymr(# select p1.id from tree as p1, tree as p2 where p1.lft between
> p2.lft and p2.rgt and p2.id = 3
> nymr(# );
>  count |  max
> ---+
>  1 | 2004-01-01 09:12:12+00
> (1 row)
> 
> Here is the photo_count function, photo_updates just has differnt
> attribute names/types
> 
> create function photo_count(int4) returns int4 as 'DECLARE
> gallery_id alias for $1;
> pcount int4;
> begin
>   select count(pid) into pcount from photos where id in (
> select p1.id from tree as p1, tree as p2 where p1.lft between p2.lft
> and p2.rgt and p2.id = gallery_id
>   );
>   return pcount;
> end' language 'plpgsql';
> 
> 
> 
> --
> Gary Stainburn
> 
> This email does not contain private or confidential material as it
> may be snooped on by interested government parties for unknown
> and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
> 
> ---(end of broadcast)---
> TIP 3: if posting/reading through Usenet, please send an appropriate
>   subscribe-nomail command to [EMAIL PROTECTED] so that your
>   message can get through to the mailing list cleanly
> 


-- 
Mike Rylander
[EMAIL PROTECTED]
GPLS -- PINES Development
Database Developer

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] Recursive SETOF function

2004-11-22 Thread Mike Rylander 
I'm feeling sausey today, so here is my (untested) attempt to
translate your function.  It's inline below, and you'll want to look
here http://www.postgresql.org/docs/7.4/interactive/plpgsql.html for
more information.

On Mon, 22 Nov 2004 09:18:13 -0600, Richard Rowell
<[EMAIL PROTECTED]> wrote:
> I'm trying to port some TSQL to PLPGSQL.  The DB has a table with a
> recursive foreign key that represents a tree hierarchy.  I'm trying to
> re-create a TSQL function that pulls out all the ancestors of a given
> node in the hierarchy.
> 
> I'm rather new to PLSQL and I have several questions.
> 
> 1.  In TSQL, I can assign a scalar to the result of query like so:
>   SET @var1 = (SELECT foo FROM bar WHERE [EMAIL PROTECTED])
> 
> How would I do this in PLSQL?
> 
> 2.  In TSQL the "result table" can be inserted into manually.  IE:
> 
> CREATE FUNCTION foo () RETURNS @ttable TABLE( uid INTEGER) AS BEGIN
> INSERT @ttable VALUES (1)
> RETURN
> END
> 
> Is there a way to manually insert rows into the result table in PLSQL?
> 
> What follows is my TSQL function if that helps give context.
> 
> CREATE FUNCTION svp_getparentproviderids (@child_provider INTEGER)
> RETURNS @provider_ids TABLE ( uid INTEGER )
> AS
> BEGIN
> DECLARE @cid AS INTEGER
> IF (SELECT count(*) FROM providers WHERE uid [EMAIL PROTECTED]) > 0
> BEGIN
> SET @cid = @child_provider
> WHILE @cid IS NOT NULL
> BEGIN
> INSERT @provider_ids VALUES (@cid)
> SET @cid = (SELECT parent_id FROM providers WHERE [EMAIL 
> PROTECTED])
> END
> END
> RETURN
> END
> 

-- This TYPE will get you a named column... easier to use SRFs with a
preexisting type.
CREATE TYPE svp_getparentproviderids_uid_type AS ( uid INTEGER );

CREATE FUNCTION svp_getparentproviderids (INTEGER)
   RETURNS SETOF svp_getparentproviderids_uid_type
   AS '
DECLARE
  child_provider ALIAS FOR $1;
  cid INTEGER;
BEGIN
SELECT count(*) FROM providers WHERE uid [EMAIL PROTECTED]) > 0
LOOP
cid := child_provider
IF cid IS NULL THEN
  EXIT;
END IF;
RETURN NEXT cid;
SELECT INTO cid parent_id FROM providers WHERE [EMAIL PROTECTED];
END LOOP;
RETURN
END;' LANGUAGE 'plpgsql';


Hope that helps!

> --
> 
> ---(end of broadcast)---
> TIP 3: if posting/reading through Usenet, please send an appropriate
>   subscribe-nomail command to [EMAIL PROTECTED] so that your
>   message can get through to the mailing list cleanly
> 


-- 
Mike Rylander
[EMAIL PROTECTED]
GPLS -- PINES Development
Database Developer

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] Recursive SETOF function

2004-11-22 Thread Mike Rylander 
Forgot one line.  See below

On Mon, 22 Nov 2004 11:54:30 -0500, Mike Rylander <[EMAIL PROTECTED]> wrote:
> I'm feeling sausey today, so here is my (untested) attempt to
[snip]
> > CREATE FUNCTION svp_getparentproviderids (@child_provider INTEGER)
> > RETURNS @provider_ids TABLE ( uid INTEGER )
> > AS
> > BEGIN
> > DECLARE @cid AS INTEGER
> > IF (SELECT count(*) FROM providers WHERE uid [EMAIL PROTECTED]) > 0
> > BEGIN
> > SET @cid = @child_provider
> > WHILE @cid IS NOT NULL
> > BEGIN
> > INSERT @provider_ids VALUES (@cid)
> > SET @cid = (SELECT parent_id FROM providers WHERE [EMAIL 
> > PROTECTED])
> > END
> > END
> > RETURN
> > END
> >
> 
> -- This TYPE will get you a named column... easier to use SRFs with a
> preexisting type.
> CREATE TYPE svp_getparentproviderids_uid_type AS ( uid INTEGER );
> 
> CREATE FUNCTION svp_getparentproviderids (INTEGER)
>RETURNS SETOF svp_getparentproviderids_uid_type
>AS '
> DECLARE
>   child_provider ALIAS FOR $1;
>   cid INTEGER;
> BEGIN

SELECT * FROM providers WHERE uid [EMAIL PROTECTED]) > 0
IF NOT FOUND
RETURN;
END IF;

> LOOP
> cid := child_provider
> IF cid IS NULL THEN
>   EXIT;
> END IF;
> RETURN NEXT cid;
> SELECT INTO cid parent_id FROM providers WHERE [EMAIL PROTECTED];
> END LOOP;
> RETURN
> END;' LANGUAGE 'plpgsql';
> 
> Hope that helps!
> 
> > --
> 
> 
> >
> > ---(end of broadcast)---
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> >   subscribe-nomail command to [EMAIL PROTECTED] so that your
> >   message can get through to the mailing list cleanly
> >
> 
> 
> --
> Mike Rylander
> [EMAIL PROTECTED]
> GPLS -- PINES Development
> Database Developer
> 


-- 
Mike Rylander
[EMAIL PROTECTED]
GPLS -- PINES Development
Database Developer

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [postgres] Re: [SQL] Recursive SETOF function

2004-11-22 Thread Mike Rylander 
Sorry about that... try this:

CREATE OR REPLACE FUNCTION svp_getparentproviderids
(INTEGER)
  RETURNS SETOF svp_getparentproviderids_uid_type
  AS '
DECLARE
 child_provider ALIAS FOR $1;
 cid svp_getparentproviderids_uid_type%ROWTYPE;
 tmp_cid INTEGER;
BEGIN
   SELECT INTO tmp_cid count(*) FROM providers WHERE uid =child_provider;
   IF tmp_cid = 0 THEN
   RAISE EXCEPTION ''Inexistent ID --> %'', child_provider;
   RETURN;
   END IF;
   cid.uid := child_provider;
   LOOP
   EXIT WHEN tmp_cid IS NULL;
   RETURN NEXT cid;
   SELECT INTO tmp_cid parent_id FROM providers WHERE uid=cid.uid;
   END LOOP;
   RETURN;
END;' LANGUAGE 'plpgsql';

On Mon, 22 Nov 2004 12:51:41 -0600, Richard Rowell
<[EMAIL PROTECTED]> wrote:
> I have been fiddling with what you sent.  I have it working mostly, save
> for I keep getting syntax errors on the "RETURN NEXT cid;" line.  If I
> remove this line then the function works ( but returns nothing of
> course).  Any ideas?
> 
> sp_demo_505=# CREATE OR REPLACE FUNCTION svp_getparentproviderids
> (INTEGER)
>RETURNS SETOF svp_getparentproviderids_uid_type
>AS '
> DECLARE
>   child_provider ALIAS FOR $1;
>   cid INTEGER;
> BEGIN
> SELECT INTO cid count(*) FROM providers WHERE uid =child_provider;
> IF cid = 0 THEN
> RAISE EXCEPTION ''Inexistent ID --> %'', child_provider;
> RETURN;
> END IF;
> cid := child_provider;
> LOOP
> EXIT WHEN cid IS NULL;
> RETURN NEXT cid;
> SELECT INTO cid parent_id FROM providers WHERE uid=cid;
> END LOOP;
> RETURN;
> END;' LANGUAGE 'plpgsql';
> CREATE FUNCTION
> sp_demo_505=# select * from svp_getparentproviderids(21112);
> ERROR:  incorrect argument to RETURN NEXT at or near "cid"
> CONTEXT:  compile of PL/pgSQL function "svp_getparentproviderids" near
> line 13
> 
> 


-- 
Mike Rylander
[EMAIL PROTECTED]
GPLS -- PINES Development
Database Developer

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] Breadth first traversal in PLSQL (How to implement Queue?)

2004-12-15 Thread Mike Rylander 
On Wed, 15 Dec 2004 12:54:44 -0600, Richard Rowell
<[EMAIL PROTECTED]> wrote:
> I have a table with a unary (recursive) relationship that represents a
> hierarchy.  With the gracious help of Mike Rylander I was able to port a
> TSQL function that would traverse "up" the hierarchy to PL/SQL.  Now I
> need help porting the "down" the hierarchy function.

Glad I could help!

> 
> As implemented in TSQL I utilized a simple breadth first tree traversal.
> I'm not sure how to replicate this in PL/SQL as I haven't figured out
> how to implement the queue required for the breadth first algorithm.  My
> queue is declared "queue SETOF INTEGER" and I'm able to "SELECT INTO"
> this variable.  However when I try to delete the "current" value, I get
> a syntax error.  If I comment the delete out, I also get an error when I
> try to fetch the "next" value from the front of the queue.
> 

You probably want to use a temp table to hold the queue.  Edits inline below.

> Below is the function, followed by the psql output:
> 
> CREATE OR REPLACE FUNCTION svp_getchildproviderids (INTEGER)
>   RETURNS SETOF INTEGER
>   AS '
> DECLARE
>  parent_provider ALIAS FOR $1;
>  cid INTEGER;

 -- Comment out the next line...
 -- queue SETOF INTEGER;

> BEGIN

 -- We need to use execute to create the queue, otherwise
 -- the OID will be cached and the next invocation will cause
 -- an exception.
 EXECUTE ''CREATE TEMP TABLE cid_queue
   (id SERIAL, cid INTEGER ) WITHOUT OIDS;'';

>SELECT INTO cid count(*) FROM providers WHERE uid =parent_provider;
>IF cid = 0 THEN
>RAISE EXCEPTION ''Inexistent ID --> %'', parent_provider;
>RETURN;
>END IF;
>cid := parent_provider;
>LOOP
>EXIT WHEN cid IS NULL;
>RETURN NEXT cid;

  -- Put the CID into the queue
  EXECUTE ''INSERT INTO cid_queue VALUES
   ((SELECT uid FROM providers WHERE
parent_id = '' ||
 quote_literal( cid ) || ''));'';

  -- We'll use EXECUTE to delete the current cid from the queue
  EXECUTE ''DELETE FROM cid_queue WHERE cid = '' ||
 quote_literal( cid ) || '';'';

  -- And a short loop to grab the next one
   FOR cid IN EXECUTE ''SELECT cid FROM cid_queue ORDER BY id LIMIT 1;''
   END LOOP;

>END LOOP;
>RETURN;
> END;' LANGUAGE 'plpgsql';

Let me know if that works.  As before, it's untested, so YMMV... :)

-- 
Mike Rylander
[EMAIL PROTECTED]
GPLS -- PINES Development
Database Developer
http://open-ils.org

---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [SQL] Breadth first traversal in PLSQL (How to implement Queue?)

2004-12-15 Thread Mike Rylander 
Arg! One more change below

On Wed, 15 Dec 2004 21:48:57 -0500, Mike Rylander <[EMAIL PROTECTED]> wrote:
> On Wed, 15 Dec 2004 12:54:44 -0600, Richard Rowell
> <[EMAIL PROTECTED]> wrote:
> > I have a table with a unary (recursive) relationship that represents a
> > hierarchy.  With the gracious help of Mike Rylander I was able to port a
> > TSQL function that would traverse "up" the hierarchy to PL/SQL.  Now I
> > need help porting the "down" the hierarchy function.
> 
> Glad I could help!
> 
> >
> > As implemented in TSQL I utilized a simple breadth first tree traversal.
> > I'm not sure how to replicate this in PL/SQL as I haven't figured out
> > how to implement the queue required for the breadth first algorithm.  My
> > queue is declared "queue SETOF INTEGER" and I'm able to "SELECT INTO"
> > this variable.  However when I try to delete the "current" value, I get
> > a syntax error.  If I comment the delete out, I also get an error when I
> > try to fetch the "next" value from the front of the queue.
> >
> 
> You probably want to use a temp table to hold the queue.  Edits inline below.
> 
> > Below is the function, followed by the psql output:
> >
> > CREATE OR REPLACE FUNCTION svp_getchildproviderids (INTEGER)
> >   RETURNS SETOF INTEGER
> >   AS '
> > DECLARE
> >  parent_provider ALIAS FOR $1;
> >  cid INTEGER;
> 
>  -- Comment out the next line...
>  -- queue SETOF INTEGER;
> 
> > BEGIN
> 
>  -- We need to use execute to create the queue, otherwise
>  -- the OID will be cached and the next invocation will cause
>  -- an exception.
>  EXECUTE ''CREATE TEMP TABLE cid_queue
>(id SERIAL, cid INTEGER ) WITHOUT OIDS;'';
> 
> >SELECT INTO cid count(*) FROM providers WHERE uid =parent_provider;
> >IF cid = 0 THEN
> >RAISE EXCEPTION ''Inexistent ID --> %'', parent_provider;
> >RETURN;
> >END IF;
> >cid := parent_provider;
> >LOOP
> >EXIT WHEN cid IS NULL;
> >RETURN NEXT cid;
> 
>   -- Put the CID into the queue
>   EXECUTE ''INSERT INTO cid_queue VALUES
>((SELECT uid FROM providers WHERE
> parent_id = '' ||
>  quote_literal( cid ) || ''));'';
> 
>   -- We'll use EXECUTE to delete the current cid from the queue
>   EXECUTE ''DELETE FROM cid_queue WHERE cid = '' ||
>  quote_literal( cid ) || '';'';
> 
>   -- And a short loop to grab the next one
>FOR cid IN EXECUTE ''SELECT cid FROM cid_queue ORDER BY id LIMIT 
> 1;''
>END LOOP;
> 
> >END LOOP;

-- We need to drop the temp table, since this will probably be called
-- more than once in a transaction.
EXECUTE ''DROP TABLE cid_queue;'';

> >RETURN;
> > END;' LANGUAGE 'plpgsql';
> 
> Let me know if that works.  As before, it's untested, so YMMV... :)
> 
> --
> Mike Rylander
> [EMAIL PROTECTED]
> GPLS -- PINES Development
> Database Developer
> http://open-ils.org
> 


-- 
Mike Rylander
[EMAIL PROTECTED]
GPLS -- PINES Development
Database Developer
http://open-ils.org

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [SQL] Constraint on 2 column possible?

2005-01-27 Thread Mike Rylander 
On Thu, 27 Jan 2005 13:44:32 +0200, Andrei Bintintan <[EMAIL PROTECTED]> wrote:
>
> Hi,
>
> I have a table:
> CREATE TABLE werke1(
> id SERIAL,
> id_hr int4 NOT NULL,
> id_wk int4 NOT NULL
> );
>
> CREATE TABLE contact(
> id SERIAL,
> type varchar(20),
> );
>
>
> Now id_hr and id_wk are all referencing the same table contact(id). In the
> contact table I have another column called type.
> How can I write a constraint that checks that id_hr references contact(id)
> and the contact(type='t1')
> and that id_wk references contact(id) and the contact(type='t2').

If I understand what you want, you can do this with a multi-column
foreign key and check constraints.

CREATE TABLE werke1 (
id SERIAL,
id_hr NOT NULL,
hr_contact NOT NULL CHECK (hr_contact = 't1'),
id_wk int4 NOT NULL,
wk_contact NOT NULL CHECK (hr_contact = 't2'),
CONSTRAINT werke1_hr FOREIGN KEY (id_hr,hr_contact) references contact(id,type),
CONSTRAINT werke1_wk FOREIGN KEY (id_wk, wk_contact) references contact(id,type)
);

This will cause the FKEY to match only contact entries that have the
correct combination of id and type.

Hope that helps!

>
> More explicit: the id_hr shows to the id from contact, and this line from
> contact must have the line type='t1'. The same for id_wk just the type is
> another.
>
> I can write:
> CREATE TABLE werke1(
> id SERIAL,
> id_hr int4 NOT NULL references contact(id),
> id_wk int4 NOT NULL references contact(id)
> );
> but how do I check also the type column?
>
> Best regards,
> Andy.


--
Mike Rylander
[EMAIL PROTECTED]
GPLS -- PINES Development
Database Developer
http://open-ils.org


-- 
Mike Rylander
[EMAIL PROTECTED]
GPLS -- PINES Development
Database Developer
http://open-ils.org

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] Serial and Index

2005-02-27 Thread Mike Rylander 
On Sun, 27 Feb 2005 12:54:52 +, Sam Adams <[EMAIL PROTECTED]> wrote:
> I read at http://jamesthornton.com/postgres/FAQ/faq-english.html#4.15.1
> that when a serial is created then an index is created on the column.
> However I can't seem to find this anywhere in the PoistgreSQL manual. Is
> this true? Thanks.

The FAQ entry is incorrect.  If you make your SERIAL column the
PRIMARY KEY of the table, however, a UNIQUE index will be created.

-- 
Mike Rylander
[EMAIL PROTECTED]
GPLS -- PINES Development
Database Developer
http://open-ils.org

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [SQL] detaching triggers

2005-03-27 Thread Mike Rylander 
On Sun, 27 Mar 2005 17:41:02 +0200, Enrico Weigelt <[EMAIL PROTECTED]> wrote:
> 
> Hi folks,
> 
> is it somehow possible to detach trigger, so the calling transaction
> can return immediately, even before the trigger function has
> returned.

No, but see below.

> The only solution I currently know is to fill somethings in a queue
> table by rule and have an external daemon looking at it every second.
> But this doesnt seem very optimal for me.

Take a look at the LISTEN/NOTIFY interfaces in the docs.  This will
allow async post-transaction processing.  You can set up an ALSO rule
to notify when a particular type of statement has executed against
your target table.

http://www.postgresql.org/docs/8.0/static/sql-listen.html
http://www.postgresql.org/docs/8.0/static/sql-notify.html

See the bottom of:
http://www.postgresql.org/docs/8.0/static/sql-createrule.html

Hope that helps!

-- 
Mike Rylander
[EMAIL PROTECTED]
GPLS -- PINES Development
Database Developer
http://open-ils.org

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [SQL] gone blind - can't see syntax error

2005-04-01 Thread Mike Rylander 
On Apr 1, 2005 11:36 AM, Gary Stainburn <[EMAIL PROTECTED]> wrote:
> Hi folks.
> 
> I've been looking at this for 10 minutes and can't see what's wrong.
> Anyone care to enlighten me.
> 
> Thanks
> 
> Gary
> 
> [EMAIL PROTECTED] webroot]# psql -a -f new-view.sql
> SELECT r.r_id, r.r_registration, r.r_chassis, r.r_vehicle, r.r_fuel,
> r.r_pack_mats, r.r_delivery,
> (date(r.r_delivery) - date(now())) AS r_remaining, r.r_created,
> r.r_completed, r.r_salesman,
> sm.r_salesman as salesman_name,
> d.d_des, de.de_des,
> u.u_id, u.u_userid, u.u_username, u.u_salesman, u.u_target,
> t.t_id, t.t_des,
> s.s_id, s.s_des,
> c.c_id, c.c_des,
> co.com_count, co.com_unseen
> FROM requests r,
   ^^^
> left outer join users sm on sm.u_id = r.r_salesman,
> left outer join users u on r.r_u_id = u.u_id,
> left outer join request_types t on r.r_t_id = t.t_id,
> left outer join request_states s on r.r_s_id = s.s_id,
> left outer join dealerships d on r.r_d_id = d.d_id,
> left outer join departments de on r.r_de_id = de.de_id,
> left outer join customers c on r.r_c_id = c.c_id,
> left outer join comment_tallies co on r.r_id = co.r_id
> ORDER BY r.r_id;
> psql:new-view.sql:19: ERROR:  parser: parse error at or near "left"

Don't put commas between your joins.

-- 
Mike Rylander
[EMAIL PROTECTED]
GPLS -- PINES Development
Database Developer
http://open-ils.org

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] Speed up slow select - was gone blind

2005-04-01 Thread Mike Rylander 
Can you send the EXPLAIN ANALYZE of each?  We can't really tell where
the slowdown is without that.

On Apr 1, 2005 12:32 PM, Gary Stainburn <[EMAIL PROTECTED]> wrote:
> Hi folks.
> 
> I've got my select working now, but I haven't received the speed
> increase I'd expected.  It replaced an earlier select which combined a
> single explicit join with multiple froms.
> 
> The first select is the old one, the second  one is the new one (with a
> new join).  The new one takes 24 seconds to run while the old one took
> 29.
> 
> How can I redo the select to improve the speed, or what else can I do to
> optimaise the database?
> 
> original (ugly)
> ~
> 
> SELECT r.r_id, r.r_registration, r.r_chassis, r.r_vehicle, r.r_fuel,
> r.r_pack_mats, r.r_delivery,
> (date(r.r_delivery) - date(now())) AS r_remaining,
> r.r_created, r.r_completed, r.r_salesman, r.salesman_name,
> d.d_des, de.de_des,
> u.u_id, u.u_userid, u.u_username, u.u_salesman, u.u_target,
> t.t_id, t.t_des,
> s.s_id, s.s_des,
> c.c_id, c.c_des,
> co.com_count, co.com_unseen
> FROM (SELECT r.r_id, r.r_t_id, r.r_d_id, r.r_de_id, r.r_s_id,
> r.r_registration, r.r_chassis, r.r_c_id, r.r_vehicle,
> r.r_fuel, r.r_pack_mats, r.r_delivery, r.r_salesman,
> r.r_created, r.r_completed, r.r_u_id,
> u.u_username AS salesman_name
> FROM (requests r LEFT JOIN users u ON
> ((r.r_salesman = u.u_id r,
> users u,
> request_types t,
> request_states s,
> dealerships d,
> departments de,
> customers c,
> comment_tallies co
> WHERE   (r.r_d_id = d.d_id) AND
> (r.r_s_id = s.s_id) AND
> (r.r_c_id = c.c_id) AND
> (r.r_t_id = t.t_id) AND
> (r.r_d_id = d.d_id) AND
> (r.r_de_id = de.de_id) AND
> (r.r_u_id = u.u_id) AND
> (r.r_id = co.r_id))
> ORDER BY r.r_id;
> 
> new
> ~~~
> SELECT r.r_id, r.r_registration, r.r_chassis, r.r_vehicle, r.r_fuel,
> r.r_pack_mats, r.r_delivery,
> (date(r.r_delivery) - date(now())) AS r_remaining, r.r_created,
> r.r_completed, r.r_salesman,
> sm.u_username as salesman_name,
> d.d_des, de.de_des,
> u.u_id, u.u_userid, u.u_username, u.u_salesman, u.u_target,
> t.t_id, t.t_des,
> s.s_id, s.s_des,
> c.c_id, c.c_des,
> co.com_count, co.com_unseen,
> pl.pl_id, pl.pl_desc as plates
> FROM requests r
> left outer join users sm on sm.u_id = r.r_salesman
> left outer join users u on r.r_u_id = u.u_id
> left outer join request_types t on r.r_t_id = t.t_id
> left outer join request_states s on r.r_s_id = s.s_id
> left outer join dealerships d on r.r_d_id = d.d_id
> left outer join departments de on r.r_de_id = de.de_id
> left outer join customers c on r.r_c_id = c.c_id
> left outer join comment_tallies co on r.r_id = co.r_id
> left outer join plates pl on r.r_plates = pl.pl_id
> ORDER BY r.r_id;
> 
> --
> Gary Stainburn
> 
> This email does not contain private or confidential material as it
> may be snooped on by interested government parties for unknown
> and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
> 
> ---(end of broadcast)---
> TIP 9: the planner will ignore your desire to choose an index scan if your
>   joining column's datatypes do not match
> 


-- 
Mike Rylander
[EMAIL PROTECTED]
GPLS -- PINES Development
Database Developer
http://open-ils.org

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [SQL] ENUM like data type

2005-06-28 Thread Mike Rylander 
On 6/21/05, MRB <[EMAIL PROTECTED]> wrote:
> Hi All,
> 
> I have something in mind I'm not certain is do-able.
> 
> I'm working with a lot of data from MySQL where the MySQL ENUM type is used.
> 

MySQL's ENUM is basically a wrapper for CHECK.  You can use a CHECK
constraint like this:

CREATE TABLE test(
testfield TEXT CHECK (testfield IN( 'Bits', 'of', 'data'))
);

> This is not a big problem per se but creating the proper lookup tables
> is becoming a bit tedious so I was hoping to make something better of it.
> 
> Here is where I get uncertain as to if this is possible. My idea is to
> create a pseudo type that triggers the creation of it's lookup tables
> the same way the SERIAL type triggers creation of a sequence and returns
> an int with the right default value.

Although you can't create a generic type to handle this, you can
create a DOMAIN to wrap up your constraint for each "enum" type field
that you want:

CREATE DOMAIN fruit AS TEXT CHECK (VALUE IN ('apple','orange','banana'));
CREATE TABLE eat (
  food fruit
);

http://www.postgresql.org/docs/8.0/interactive/sql-createdomain.html

Hope that helps.

-- 
Mike Rylander
[EMAIL PROTECTED]
GPLS -- PINES Development
Database Developer
http://open-ils.org

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org