Re: [SQL]

[Plugge, Joe R.]

1 - why two databases?  Couldn't you have just created two separate SCHEMAS?
2 - if you insist on two separate databases:   
http://www.postgresql.org/docs/9.2/static/dblink.html


From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On 
Behalf Of Kaleeswaran Velu
Sent: Tuesday, October 08, 2013 11:23 AM
To: pgsql-sql@postgresql.org
Subject: [SQL]

Hi Team,
I am using PostgreSQL 9.2.3 in Windows platform. I have created two databases 
in it. Now I want to refer the tables across the databases. Meaning would like 
to create Database link. Can anyone guide me on how to create a DB link?

Thanks and Regards
Kaleeswaran Velu

Re: [SQL] generic crosstab ?

[Joe Conway]

On 04/24/2012 02:42 PM, David Johnston wrote:
> You must specify the output record structure:
> 
> SELECT crosstab(text, text) AS ( col1_name col1_type [, colN_name
> colN_type]* )
> 
> Whether this relates to the “materialization node” message you are
> receiving I have no idea.

The error is because you are selecting from a set returning function in
the target list rather than the from clause. It should be more like:

SELECT * FROM crosstab(text, text) AS ( col1_name col1_type [, colN_name
> colN_type]* )

HTH,

Joe

-- 
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support

-- 
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] When To Use Quotes

[Plugge, Joe R.]

Should match to the data type of the filtered value, so CHAR,VARCHAR,All DATE 
TYPES should be quoted.  INTEGER, BIGINT, SMALLINT should not 

-Original Message-
From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On 
Behalf Of Carlos Mennens
Sent: Thursday, January 05, 2012 7:56 AM
To: PostgreSQL (SQL)
Subject: [SQL] When To Use Quotes

I'm trying to understand when in SELECT statements should and should I not use 
single quotes to filter my results. For example:

SELECT * FROM people
WHERE fname = 'James';

or

SELECT * FROM price
WHERE msrb
BETWEEN 50 AND 100;

Now is it correct to say that in PostgreSQL or ANSI SQL in general I should use 
'single quotes' when the condition is strictly a numerical data type value and 
everything else should be inside 'single quotes'?

--
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] Compare two Data bases Structure

[Plugge, Joe R.]

Check out DB Solo ...

http://www.dbsolo.com/


Does both DDL compare as well as data compare.



From: pgsql-sql-ow...@postgresql.org [pgsql-sql-ow...@postgresql.org] On Behalf 
Of manuel antonio ochoa [manuel8aalf...@gmail.com]
Sent: Wednesday, February 23, 2011 6:03 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] Compare two Data bases Structure

How Can I do to compare two structures of data bases ?

DBA  != DBB I need wich functions and wich tables are not equals

thnks



-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] Correct usage of FOR UPDATE?

[Joe Carr]

Hello,
I have a simple table-based queue system, and I'd looking for some advice on
improving my dequeue function. it boils down to:

SELECT id
FROM queue
WHERE 
FOR UPDATE NOWAIT;

which works well, in that no item gets dequeued more that once. The issue,
however is that when a contention occurs, the error "could not obtain lock
on row in relation" is raised.

Is there a way that I can rewrite this so that when a contention occurs,
there is no error? I would like the "winning" process to lock the row, and
the "losing" process to select null rather than raise the rowlock error.

thanks beforehand for any help!

Re: [SQL] Union Question

[Plugge, Joe R.]

You may want to try dblink.

http://www.postgresql.org/docs/current/static/dblink.html


From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On 
Behalf Of Shaun McCloud
Sent: Friday, December 03, 2010 10:51 AM
To: pgsql-sql@postgresql.org
Subject: [SQL] Union Question

Hello,

I need to union three PostgreSQL tables and this won't be a problem but the 
tables are on different servers.  Basically, I have an administrative server 
that needs the tables viewable in a web administrator and three query servers 
that log the needed data locally.  Is there a way I can do this without using 
Slony-I to replicate the data to the administrative server?

Shaun McCloud - Software Testing Analyst
GeoComm Inc.
601 W. Saint Germain St., Saint Cloud, MN 56301
Office: 320.240.0040 Fax: 320.240.2389 Toll Free: 888.436.2666
click here to visit www.geo-comm.com
Microsoft Certified Desktop Support Technician (MCDST)

Do or do not, there is no try.
  -Yoda

Re: [SQL] Generating Rows from a date and a duration

[Joe Conway]

On 09/08/2010 08:24 AM, Tim Schumacher wrote:
>>> I'm kinda stuck situation, I have a timestamp which resambles a
>>> startdate and a duration in days and I want to bloat this, so I have a
>>> row for every day beginning from the startdate. I have created an
>>> example bellow, maybe I'm doing it on the wrong angle and you can come
>>> up with some better ideas:



> As you can see in my example, I'm already using it and this is my
> dilemma. Since I can not bring the values of the FROM-Table to the
> parameters of my function.

Depending on how large your base table is, this might work for you:

CREATE TABLE example
(
  id serial NOT NULL,
  startdate timestamp without time zone,
  duration int NOT NULL,
  CONSTRAINT pq_example_id PRIMARY KEY (id)
);

insert into example(id,startdate,duration) values (1,'2010-09-03',4);
insert into example(id,startdate,duration) values (2,'2010-09-03',6);

CREATE OR REPLACE FUNCTION unroll_durations()
RETURNS TABLE(
  example_id integer,
  duration_date date)
AS $$
DECLARE
  rec1 record;
  rec2 record;
BEGIN
  FOR rec1 IN SELECT id, startdate, duration
  FROM example
  LOOP
FOR rec2 IN SELECT
to_date(to_char(rec1.startdate,'-MM-DD'),'-MM-DD') + s.a as
stockdate
FROM generate_series(0, rec1.duration - 1) AS s(a)
LOOP
  example_id:= rec1.id;
  duration_date := rec2.stockdate;
  RETURN NEXT;
END LOOP;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

select * from unroll_durations();
 example_id | duration_date
+---
  1 | 2010-09-03
  1 | 2010-09-04
  1 | 2010-09-05
  1 | 2010-09-06
  2 | 2010-09-03
  2 | 2010-09-04
  2 | 2010-09-05
  2 | 2010-09-06
  2 | 2010-09-07
  2 | 2010-09-08
(10 rows)

HTH,

Joe

-- 
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support



signature.asc
Description: OpenPGP digital signature

Re: [SQL] strangest thing happened

[Joe Conway]

On 07/07/2010 12:59 PM, John wrote:
> I am the only developer, DBA etc.. for a small project.  Today (yesterday was 
> everything was perfect) many of the sequence numbers fell behind what is the 
> actual PK value.   For example the invoice PK sequence current value = 1056 
> but the table PK was 1071.  Nobody (other than myself) knows how to 
> edit/access the postgres server.  So
> 
> 1. Does anyone know how this could have happened?? Other than human 
> interaction.

I've never heard of this happening. Are you certain nothing bypassed the
sequence and directly inserted a PK value?

> 2. Does anyone have a script to reset the sequences to match the tables? 

Not heavily tested, but something like this might do the trick:

8<--
CREATE OR REPLACE FUNCTION adjust_seqs(namespace text)
  RETURNS text AS $$
DECLARE
  rec record;
  startvalbigint;
  sql text;
  seqname text;
BEGIN
  FOR rec in EXECUTE 'select table_name, column_name, column_default
  from information_schema.columns
  where table_schema = ''' || namespace || '''
  and column_default like ''nextval%''' LOOP

seqname := pg_get_serial_sequence(rec.table_name, rec.column_name);
sql := 'select max(' || rec.column_name || ') + 1 from ' ||
rec.table_name;
EXECUTE sql INTO startval;
IF startval IS NOT NULL THEN
  sql := 'ALTER SEQUENCE ' || seqname || ' RESTART WITH ' ||
  startval;
  EXECUTE sql;
  RAISE NOTICE '%', sql;
    END IF;
  END LOOP;
  RETURN 'OK';
END;
$$ LANGUAGE plpgsql STRICT;

select adjust_seqs('public');
8<--

HTH,

Joe

-- 
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & Support



signature.asc
Description: OpenPGP digital signature

Re: [SQL] how to construct sql

[Plugge, Joe R.]

This is discussed in this Wiki:


http://wiki.postgresql.org/wiki/Grouping_Sets



-Original Message-
From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On 
Behalf Of Hiltibidal, Rob
Sent: Wednesday, June 02, 2010 12:06 PM
To: Oliveiros; Wes James; pgsql-sql@postgresql.org
Subject: Re: [SQL] how to construct sql

db2 has a group by rollup function.. does this exist in postgres?

-Original Message-
From: pgsql-sql-ow...@postgresql.org
[mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Oliveiros
Sent: Wednesday, June 02, 2010 11:55 AM
To: Wes James; pgsql-sql@postgresql.org
Subject: Re: [SQL] how to construct sql

Hi,
Have you already tried this out?

select MAX(page_count_count) - MIN(page_count_count)  
from page_count 
group by page_count_pdate.


Best,
Oliveiros

- Original Message - 
From: "Wes James" 
To: 
Sent: Wednesday, June 02, 2010 5:48 PM
Subject: [SQL] how to construct sql


>I am grabbing a printer total and putting it in a table.  The
> page_count is continuously increasing:
> 
> page_count_countpage_count_pdate
> 10   2010-05-10
> 20   2010-05-10
> 40   2010-05-11
> 60   2010-05-11
> 80   2010-05-11
> 100   2010-05-12
> 120   2010-05-12
> .
> 
> and so on.
> 
> I can do:
> 
> select sum(page_count_count) from page_count group by
page_count_pdate.
> 
> and get a total for a day.  But this is not the total I want.  I want
> the total page count for the day.  This would mean getting the first
> page count of the day and then subtracting that from last page_count
> for the day.  For 2010-05-11 above it would be
> 
> 80 - 40 = 40 total for the day.  Is there a way to do this with sql?
> 
> thx,
> 
> -wes
> 
> -- 
> 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

PRIVILEGED AND CONFIDENTIAL
This email transmission contains privileged and confidential information 
intended only for the use of the individual or entity named above.  If the 
reader of the email is not the intended recipient or the employee or agent 
responsible for delivering it to the intended recipient, you are hereby 
notified that any use, dissemination or copying of this email transmission is 
strictly prohibited by the sender.  If you have received this transmission in 
error, please delete the email and immediately notify the sender via the email 
return address or mailto:postmas...@argushealth.com.  Thank you.





-- 
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] Column Specific Update Trigger Routine

[Plugge, Joe R.]

This is what I have and it seems to work:


CREATE OR REPLACE FUNCTION holly_unlock() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
IF OLD.password != NEW.password
THEN
UPDATE hms_mtusers_rw set loginfailedcount = 0 WHERE userid = 
OLD.userid and ownerid = OLD.ownerid;
RETURN NEW;
END IF;

RETURN NULL; -- result is ignored since this is an AFTER trigger
END;
$$;


Thanks !!

From: Justin Graf [mailto:jus...@magwerks.com]
Sent: Thursday, May 06, 2010 3:59 PM
To: Plugge, Joe R.; pgsql-sql@postgresql.org
Subject: Re: [SQL] Column Specific Update Trigger Routine

On 5/6/2010 4:12 PM, Plugge, Joe R. wrote:
I am trying to create a update trigger on a table that basically will only fire 
when a specific column is updated.  I am using version 8.4.3.

My plan of attack was to always fire on any row update, and pass in the OLD and 
NEW column that I want to check.

CREATE TRIGGER check_lockout
AFTER UPDATE ON acct_table
FOR EACH ROW
EXECUTE PROCEDURE 
acct_unlock(OLD.userid,OLD.ownerid,OLD.password,NEW.password);


This fails with :

[postg...@linux1505 ~]$ cat check_lockout_trig.sql | psql testdb
ERROR:  syntax error at or near "OLD"
LINE 4: EXECUTE PROCEDURE 
acct_unlock(OLD.userid,OLD.ownerid,OLD.password,NEW.password);


What am I doing wrong? Or is there a better way to go about this?



You don't call the trigger procedure with the old and new as parameters

new and old are automatically created for the function  acct_unlock()
CREATE TRIGGER check_lockout
AFTER UPDATE ON acct_table
FOR EACH ROW
EXECUTE PROCEDURE acct_unlock();


Next the trigger function would look something like this

create or replace function acct_unlock()
returns trigger as
$$

if  (OLD.userid <> NEW.password)
do  something
end if;

$$


keep in mind the acct_unlock must be  returns trigger

Then return either NEW or the OLD record
OLD if  not changing the record or NEW if  the updated values are to be stored 
in the table.



All legitimate Magwerks Corporation quotations are sent in a .PDF file 
attachment with a unique ID number generated by our proprietary quotation 
system. Quotations received via any other form of communication will not be 
honored.

CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally 
privileged, confidential or other information proprietary to Magwerks 
Corporation and is intended solely for the use of the individual to whom it 
addresses. If the reader of this e-mail is not the intended recipient or 
authorized agent, the reader is hereby notified that any unauthorized viewing, 
dissemination, distribution or copying of this e-mail is strictly prohibited. 
If you have received this e-mail in error, please notify the sender by replying 
to this message and destroy all occurrences of this e-mail immediately.
Thank you.

Re: [SQL] Column Specific Update Trigger Routine

[Plugge, Joe R.]

Nevermind all, I figured it out 

Thanks Dmitriy ...

From: Dmitriy Igrishin [mailto:dmit...@gmail.com]
Sent: Thursday, May 06, 2010 3:25 PM
To: Plugge, Joe R.
Subject: Re: [SQL] Column Specific Update Trigger Routine

Hey Plugge,

You dont need to pass OLD.* or NEW.* to the trigger function.
These structures available from within trigger function.
Note, that in PostgreSQL 9 you will able to create trigger
which call function only when some column of the table
affected.

Regards,
Dmitriy
2010/5/7 Plugge, Joe R. mailto:jrplu...@west.com>>
I am trying to create a update trigger on a table that basically will only fire 
when a specific column is updated.  I am using version 8.4.3.

My plan of attack was to always fire on any row update, and pass in the OLD and 
NEW column that I want to check.

CREATE TRIGGER check_lockout
AFTER UPDATE ON acct_table
FOR EACH ROW
EXECUTE PROCEDURE 
acct_unlock(OLD.userid,OLD.ownerid,OLD.password,NEW.password);


This fails with :

[postg...@linux1505 ~]$ cat check_lockout_trig.sql | psql testdb
ERROR:  syntax error at or near "OLD"
LINE 4: EXECUTE PROCEDURE 
acct_unlock(OLD.userid,OLD.ownerid,OLD.password,NEW.password);


What am I doing wrong? Or is there a better way to go about this?

[SQL] Column Specific Update Trigger Routine

[Plugge, Joe R.]

I am trying to create a update trigger on a table that basically will only fire 
when a specific column is updated.  I am using version 8.4.3.

My plan of attack was to always fire on any row update, and pass in the OLD and 
NEW column that I want to check.

CREATE TRIGGER check_lockout
AFTER UPDATE ON acct_table
FOR EACH ROW
EXECUTE PROCEDURE 
acct_unlock(OLD.userid,OLD.ownerid,OLD.password,NEW.password);


This fails with :

[postg...@linux1505 ~]$ cat check_lockout_trig.sql | psql testdb
ERROR:  syntax error at or near "OLD"
LINE 4: EXECUTE PROCEDURE 
acct_unlock(OLD.userid,OLD.ownerid,OLD.password,NEW.password);


What am I doing wrong? Or is there a better way to go about this?

Re: [SQL] understanding select into

[Plugge, Joe R.]

Johnf,



I would think that the _p, _test, _r etc are local variables within the 
procedure/function and this is the way that the value (from the select)  gets 
assigned to that local variable.



-Original Message-
From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On 
Behalf Of John
Sent: Friday, April 09, 2010 12:19 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] understanding select into



Hi,

I am reviewing a function written by some xTuple guys.  What is interesting

about it is it uses the "INTO" statement like



select something into _p from sometable where somecriteria.



The function contiunes and uses the data retreived

_p.somefield_name



And then the function ends.





Ok my question:



I also thought the select "into" created a real table.  But after running the

function the table does not exist.  I see no where that a 'drop' is issued.

In fact the function uses lot's of select into's like (_test, _r, etc..).  So

would some kind soul explain what is happening.



Could it be that "_p" is drop automaticly when the function ends?  Something

to do with scope.



Could it have something to do with the fact the function returns only an

integer?  And that causes the table to be drop.



As you can see I'm lost here!





Johnf



--

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] Simple aggregate query brain fart

[Plugge, Joe R.]

Mark, 

Change your query to this:

SELECT id, count(*) FROM mytable GROUP BY id  HAVING count(*) > 2;

-Original Message-
From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On 
Behalf Of Mark Fenbers
Sent: Thursday, March 18, 2010 10:07 AM
To: pgsql-sql@postgresql.org
Subject: [SQL] Simple aggregate query brain fart

I want to do:

SELECT id, count(*) FROM mytable WHERE count(*) > 2 GROUP BY id;

But this doesn't work because Pg won't allow aggregate functions in a where 
clause.  So I modified it to:

SELECT id, count(*) AS cnt FROM mytable WHERE cnt > 2 GROUP BY id;

But Pg still complains (that column cnt does not exist).  When using an 
GROUP/ORDER BY clause, I can refer to a column number (e.g., GROUP BY 1) 
instead of a column name, but how can I refer to my unnamed second column in my 
where clause?

Mark


-- 
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] PostgreSQL Active-Active Configuration

[Plugge, Joe R.]

You may want to try Bucardo ...   By performance, are you referring to latency? 
 If so, bandwidth between sites typically is the factor with latency in any 
replication solution.

http://bucardo.org/


-Original Message-
From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On 
Behalf Of Dave Clements
Sent: Friday, March 05, 2010 1:25 AM
To: pgsql-ad...@postgresql.org; pgsql-sql
Subject: [SQL] PostgreSQL Active-Active Configuration

Hi everyone,

I am looking for some Master-Master replication solutions for
PostgreSQL database. Please let me know if you are using one and if
you are happy with the performance.



Thanks

-- 
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] Function Syntax Help

[Plugge, Joe R.]

Thanks, I changed my code to this, it compiled, and it seems to be running now:

CREATE OR REPLACE FUNCTION gen_simultaneous_calls(mystart timestamp, mystop 
timestamp) RETURNS VOID AS $$
DECLARE
mycount integer;
newstart timestamp := mystart;
newstop timestamp := mystop;
BEGIN
WHILE newstart < newstop + INTERVAL '1 day' LOOP
SELECT INTO mycount  count(*) FROM log_calls WHERE startdate < newstart and 
enddate > newstop;
INSERT INTO simultaneous_calls_rpt (startdate,call_count) VALUES 
(newstart,mycount);
newstart := newstart + INTERVAL '1 minute';
newstop  := newstop + INTERVAL '1 minute';
END LOOP;
END;
$$ LANGUAGE 'plpgsql' VOLATILE;



From: epai...@googlemail.com [mailto:epai...@googlemail.com] On Behalf Of Brian 
Modra
Sent: Friday, October 30, 2009 2:46 PM
To: Plugge, Joe R.
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Function Syntax Help

2009/10/30 Plugge, Joe R. mailto:jrplu...@west.com>>
Thanks Brian, I changed it to this:

CREATE FUNCTION gen_simultaneous_calls(mystart timestamp, mystop timestamp) 
RETURNS VOID AS $$
DECLARE
mycount integer;
BEGIN
WHILE mystart < mystop + INTERVAL '1 day' LOOP
SELECT INTO mycount  count(*) FROM log_calls WHERE startdate < mystart and 
enddate > mystop;
INSERT INTO simultaneous_calls_rpt (startdate,call_count) VALUES 
(mystart,mycount);
mystart := mystart + INTERVAL '1 minute';
mystop  := mystop + INTERVAL '1 minute';
END LOOP;
END;
$$ LANGUAGE 'plpgsql' STABLE;

But now am getting a different error:

[postg...@linux1559 ~]$ cat gen_simultaneous_calls.sql | psql holly
ERROR:  "$1" is declared CONSTANT
CONTEXT:  compilation of PL/pgSQL function "gen_simultaneous_calls" near line 7

 mystart and mystop are constants...

you could declare variables and copy those into them, and the modify the new 
variables...



From: epai...@googlemail.com<mailto:epai...@googlemail.com> 
[mailto:epai...@googlemail.com<mailto:epai...@googlemail.com>] On Behalf Of 
Brian Modra
Sent: Friday, October 30, 2009 2:29 PM
To: Plugge, Joe R.
Cc: pgsql-sql@postgresql.org<mailto:pgsql-sql@postgresql.org>
Subject: Re: [SQL] Function Syntax Help

2009/10/30 Plugge, Joe R. mailto:jrplu...@west.com>>
I am trying to create a function that will grind through a cdr table and 
populate another table.  I am trying to load the function and am getting the 
following error:


ERROR:  function result type must be specified





CREATE FUNCTION gen_simultaneous_calls(mystart timestamp, mystop timestamp) AS 
$$ DECLARE

you need to tell it the return type. If there is none, "returns void"

e.g.
 CREATE FUNCTION gen_simultaneous_calls(mystart timestamp, mystop timestamp) 
returns void AS $$

mycount integer;

BEGIN

WHILE mystart < mystop + INTERVAL '1 day' LOOP

SELECT INTO mycount  count(*) FROM log_calls WHERE startdate < mystart and 
enddate > mystop;

INSERT INTO simultaneous_calls_rpt (startdate,call_count) VALUES 
(mystart,mycount);

mystart := mystart + INTERVAL '1 minute';

mystop  := mystop + INTERVAL '1 minute';

END LOOP;

END;

$$ LANGUAGE 'plpgsql' STABLE;








Joe R. Plugge
Database Administrator, West Interactive Corporation
11650 Miracle Hills Drive, Omaha NE 68154
402-716-0349 | Cell 402-517-2710 | jrplu...@west.com<mailto:jrplu...@west.com>

This electronic message transmission, including any attachments, contains 
information from West Corporation which may be confidential or privileged. The 
information is intended to be for the use of the individual or entity named 
above. If you are not the intended recipient, be aware that any disclosure, 
copying, distribution or use of the contents of this information is prohibited.

If you have received this electronic transmission in error, please notify the 
sender immediately by a "reply to sender only" message and destroy all 
electronic and hard copies of the communication, including attachments.



--
Brian Modra   Land line: +27 23 5411 462
Mobile: +27 79 69 77 082
5 Jan Louw Str, Prince Albert, 6930
Postal: P.O. Box 2, Prince Albert 6930
South Africa
http://www.zwartberg.com/



--
Brian Modra   Land line: +27 23 5411 462
Mobile: +27 79 69 77 082
5 Jan Louw Str, Prince Albert, 6930
Postal: P.O. Box 2, Prince Albert 6930
South Africa
http://www.zwartberg.com/

Re: [SQL] Function Syntax Help

[Plugge, Joe R.]

Thanks Brian, I changed it to this:

CREATE FUNCTION gen_simultaneous_calls(mystart timestamp, mystop timestamp) 
RETURNS VOID AS $$
DECLARE
mycount integer;
BEGIN
WHILE mystart < mystop + INTERVAL '1 day' LOOP
SELECT INTO mycount  count(*) FROM log_calls WHERE startdate < mystart and 
enddate > mystop;
INSERT INTO simultaneous_calls_rpt (startdate,call_count) VALUES 
(mystart,mycount);
mystart := mystart + INTERVAL '1 minute';
mystop  := mystop + INTERVAL '1 minute';
END LOOP;
END;
$$ LANGUAGE 'plpgsql' STABLE;

But now am getting a different error:

[postg...@linux1559 ~]$ cat gen_simultaneous_calls.sql | psql holly
ERROR:  "$1" is declared CONSTANT
CONTEXT:  compilation of PL/pgSQL function "gen_simultaneous_calls" near line 7



From: epai...@googlemail.com [mailto:epai...@googlemail.com] On Behalf Of Brian 
Modra
Sent: Friday, October 30, 2009 2:29 PM
To: Plugge, Joe R.
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Function Syntax Help

2009/10/30 Plugge, Joe R. mailto:jrplu...@west.com>>
I am trying to create a function that will grind through a cdr table and 
populate another table.  I am trying to load the function and am getting the 
following error:


ERROR:  function result type must be specified





CREATE FUNCTION gen_simultaneous_calls(mystart timestamp, mystop timestamp) AS 
$$ DECLARE

you need to tell it the return type. If there is none, "returns void"

e.g.
 CREATE FUNCTION gen_simultaneous_calls(mystart timestamp, mystop timestamp) 
returns void AS $$

mycount integer;

BEGIN

WHILE mystart < mystop + INTERVAL '1 day' LOOP

SELECT INTO mycount  count(*) FROM log_calls WHERE startdate < mystart and 
enddate > mystop;

INSERT INTO simultaneous_calls_rpt (startdate,call_count) VALUES 
(mystart,mycount);

mystart := mystart + INTERVAL '1 minute';

mystop  := mystop + INTERVAL '1 minute';

END LOOP;

END;

$$ LANGUAGE 'plpgsql' STABLE;








Joe R. Plugge
Database Administrator, West Interactive Corporation
11650 Miracle Hills Drive, Omaha NE 68154
402-716-0349 | Cell 402-517-2710 | jrplu...@west.com<mailto:jrplu...@west.com>

This electronic message transmission, including any attachments, contains 
information from West Corporation which may be confidential or privileged. The 
information is intended to be for the use of the individual or entity named 
above. If you are not the intended recipient, be aware that any disclosure, 
copying, distribution or use of the contents of this information is prohibited.

If you have received this electronic transmission in error, please notify the 
sender immediately by a "reply to sender only" message and destroy all 
electronic and hard copies of the communication, including attachments.



--
Brian Modra   Land line: +27 23 5411 462
Mobile: +27 79 69 77 082
5 Jan Louw Str, Prince Albert, 6930
Postal: P.O. Box 2, Prince Albert 6930
South Africa
http://www.zwartberg.com/

[SQL] Function Syntax Help

[Plugge, Joe R.]

I am trying to create a function that will grind through a cdr table and 
populate another table.  I am trying to load the function and am getting the 
following error:


ERROR:  function result type must be specified





CREATE FUNCTION gen_simultaneous_calls(mystart timestamp, mystop timestamp) AS 
$$ DECLARE

mycount integer;

BEGIN

WHILE mystart < mystop + INTERVAL '1 day' LOOP

SELECT INTO mycount  count(*) FROM log_calls WHERE startdate < mystart and 
enddate > mystop;

INSERT INTO simultaneous_calls_rpt (startdate,call_count) VALUES 
(mystart,mycount);

mystart := mystart + INTERVAL '1 minute';

mystop  := mystop + INTERVAL '1 minute';

END LOOP;

END;

$$ LANGUAGE 'plpgsql' STABLE;








[cid:image002.jpg@01CA596B.59B9EC10]Joe R. Plugge
Database Administrator, West Interactive Corporation
11650 Miracle Hills Drive, Omaha NE 68154
402-716-0349 | Cell 402-517-2710 | jrplu...@west.com

This electronic message transmission, including any attachments, contains 
information from West Corporation which may be confidential or privileged. The 
information is intended to be for the use of the individual or entity named 
above. If you are not the intended recipient, be aware that any disclosure, 
copying, distribution or use of the contents of this information is prohibited.

If you have received this electronic transmission in error, please notify the 
sender immediately by a "reply to sender only" message and destroy all 
electronic and hard copies of the communication, including attachments.
<><>

Re: [SQL] grouping/clustering query

[Joe]

Steve Midgley wrote:

# (invoiceid, txid)
(A, 1)
(A, 3)
(B, 1)
(B, 2)
(C, 5)
(D, 6)
(D, 7)
(E, 8)
(F, 8)

For journalling, I need to group/cluster this together. Is there a SQL
query that can generate this output:

# (journal: invoiceids, txids)
[A,B] , [1,2,3]
[C], [5]
[D], [6,7]
[E,F], [8]


Hi Dave,

I'm not following the logic here. A has 1,3 and B has 1,2. So why does 
the first line print:



[A,B] , [1,2,3]


What's the rule that tells the query to output this way? Is it that 
all of B's values are between A's values?


From a purely accounting standpoint, since transaction 1 was applied to 
both invoices A and B, you need to group the invoices so that you can 
compare total invoiced against total paid.


Joe

--
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] ORDER BY collation order

[Joe]

Hi Scott,

Scott Marlowe wrote:

Sorry, I mentioned the wrong operator before, it's ~>~ and ~<~ (asc
versus desc):

smarlowe=# create table col_test (a text);
CREATE TABLE
smarlowe=# insert into col_test (a) values ('"quoted"'),('Abc'),('123');
INSERT 0 3
smarlowe=# select * from col_test order by a;
a
--
 123
 Abc
 "quoted"
(3 rows)

smarlowe=# select * from col_test order by a using ~<~;
a
--
 "quoted"
 123
 Abc
(3 rows)

smarlowe=# select * from col_test order by a using ~>~;
a
--
 Abc
 123
 "quoted"
(3 rows)


Those operators give me "C"-style collation in the database that is 
using "en_US" collation, but what I would really prefer is the reverse.  
BTW, where are those operators documented?  Neither Google nor Yahoo nor 
postgresql.org search return anything.


Joe

--
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] ORDER BY collation order

[Joe]

Hi Scott,

Scott Marlowe wrote:

no, not encoding, locale, such as en_US or C determine sort order.
  


OK, so I guess you're saying that whatever was in the LC_COLLATE 
environment variable at the time the template0 database was created 
determines the collation/sort order?  Is that stored and visible somewhere?



You can use varchar_pattern_ops and ~*~ operator.

Search for those in the docs.
  


What I found 
(http://www.postgresql.org/docs/8.2/static/indexes-opclass.html), talks 
about creating an index with varchar_pattern_ops but that presumably 
won't affect an ORDER BY result.  I'm not quite sure where to find the 
"~*~" operator, although I did find similar ones in 9.7 Pattern 
Matching.  In any case, I'm not sure how an operator helps in changing 
an ORDER BY result from


"quoted"
123
Abc

to

123
Abc
"quoted"

It's even trickier than this simple example, because on Debian which is 
using the en_US locale, the double quotes are disregarded for ordering 
purposes, e.g.,


Medical
"Meet"
Message

Joe

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] ORDER BY collation order

[Joe]

Hi,

I just found that two identical queries on two PG 8.2.7 databases with 
the same data and same encoding, one running on Debian and the other on 
FreeBSD, returned rows in a different order, even though both queries 
had an ORDER BY clause.  Essentially, on FreeBSD a varchar starting with 
a double-quote character came first, ahead of a string starting with a 
number and a string starting with 'A', whereas on Debian the 
double-quote came last.


Some research led to the following paragraph in the documentation:

Character-string data is sorted according to the locale-specific 
collation order that was established when the database cluster was 
initialized.


I guess that means the encoding of the respective template0 database is 
what determines the sort order (UTF8 on FreeBSD and LATIN1 on Debian), 
right?  Unfortunately, I'm unable to change the FreeBSD cluster since 
it's shared with others at our hosting provider.  Is there some way to 
override the cluster setting, or plans to allow for database-specific 
collation orders?


Joe

--
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] Cursor

[Joe]

Xavier Bermeo wrote:

Hi, guys...
 
I have  problems with cursosrs.
 
Anyone have an example complete the how  load and read each position 
of a cursor?
 
I wait your answer
 
Thanks...guys


Assuming you're using ECPG, there are a couple of examples in the 
documentation, e.g.,


http://www.postgresql.org/docs/8.3/static/ecpg-commands.html
(see Select using cursors:

http://www.postgresql.org/docs/8.3/static/ecpg-variables.html#AEN33442

The essence is you DECLARE the cursor, OPEN it and then FETCH rows in 
some kind of loop, and finish by CLOSE cursor.  To exit the loop, you 
can check sqlca.sqlcode for 100 (NOT FOUND) or a SQLSTATE of 02000.


Joe

--
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] bcp.exe Fix Triggers

[Joe]

yazicivo wrote:


But executing this procedure fails for some reason I couldn't
understand.

  > SELECT public.create_bcp_fix_triggers('commsrv');
  ERROR:  syntax error at or near "AS $"
  LINE 4: RETURNS "trigger" AS $bcp-fix$
^



Use an underscore (_) instead of a minus (-), i.e., $bcp_fix$.

Joe

--
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] Protection from SQL injection

[Joe]

Ivan Sergio Borgonovo wrote:

That's better than nothing but it is still a lot of code duplication.
You've to write column names in the sql statement and in the array
and... column values are not contextual to the statement.
  


The apparent duplication in the example stems for its tutorial nature. 
In a real program, the namedict "array" (it's actually a Python 
tuple--an immutable array) would normally be constructed 
programmatically from user or other input. Note also that although 
Joshua chose to use dictionary keys named identical to the PG column 
names, they could be named differently, like "first" and "last".

That's easy... while what I wrote above does look as requiring a
really special parser.

Furthermore from the example it looks as if all this is going to
miss the scope to prevent sql injection since it doesn't support
prepared statements.
  


I assume you didn't check the PEP 249 
(http://www.python.org/dev/peps/pep-0249/). The execute() and 
executemany() Cursor object methods are precisely to prepare and execute 
database operations.


Joe

--
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] Protection from SQL injection

[Joe]

Ivan Sergio Borgonovo wrote:

It'd be nice to have a wrapper that let you write prepared statements
this way:

"select a.id, b.name from a join b on a.id=b.id where
a.status=$variable1 and b.id>$variable2 etc... but that's a pretty
good change to any language parser.
  


Python already supports something like that. See PEP 249 
(http://www.python.org/dev/peps/pep-0249/), under Module Interface, the 
description of the paramstyle parameter. Psycopg2 supports both the 
"format" (C printf) and "pyformat" styles. See the last section on this 
page for an example using the pyformat style: 
http://www.devx.com/opensource/Article/29071/0/page/3.


Joe

--
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] Select into

[Joe]

Gurjeet Singh wrote:
Except that it doesn't work... Did you try to execute that query; I am 
assuming not.
Of course I did, do you think I create results by editing them into my 
email?


The script:

delete from t1;
insert into t1 values (1, 123, 'first record');
insert into t1 values (2, 456, 'second record');
insert into t1 values (3, 789, 'third record');
select * from t1;
update t1  set col2 = t1copy.col2, col3 = t1copy.col3
from t1 as t1copy
where t1.col1 = 1 and t1copy.col1 = 3;
select * from t1;
select version();

The output:

DELETE 3
INSERT 0 1
INSERT 0 1
INSERT 0 1
col1 | col2 | col3
--+--+---
   1 |  123 | first record
   2 |  456 | second record
   3 |  789 | third record
(3 rows)

UPDATE 1
col1 | col2 | col3
--+--+---
   2 |  456 | second record
   3 |  789 | third record
   1 |  789 | third record
(3 rows)

   version

PostgreSQL 8.1.9 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.0.3
(1 row)

And BTW, I also tried your UPDATE SET (salary, name)  but that only 
works on PG 8.2 and above.  I don't see why my query would fail in 
subsequent releases.


Joe

--
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] Select into

[Joe]

Gavin 'Beau' Baumanis wrote:


The copy is inside the same table, so I don't understand why it (the 
required query ) would require any joins.


Ie. I want to copy the contents of a row (but for the id column - of 
course) into a record in the same table.


I think what you want is something like this:

Given (col1 being the id or PK):

col1 | col2 | col3
--+--+---
   1 |  123 | first record
   2 |  456 | second record
   3 |  789 | third record

then

update t1  set col2 = t1copy.col2, col3 = t1copy.col3
from t1 as t1copy
where t1.col1 = 1 and t1copy.col1 = 3;

will result in:

col1 | col2 | col3
--+--+---
   1 |  789 | third record
   2 |  456 | second record
   3 |  789 | third record

So, it is a join ... of a table with a virtual copy of itself.

Joe

--
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] UTF8 encoding and non-text data types

[Joe]

Tom Lane wrote:

Oh?  Interesting.  But even if we wanted to teach Postgres about that,
wouldn't there be a pretty strong risk of getting confused by Arabic's
right-to-left writing direction?  Wouldn't be real helpful if the entry
came out as 4321 when the user wanted 1234.  Definitely seems like
something that had better be left to the application side, where there's
more context about what the string means.
  
The Arabic language is written right-to-left, except ... when it comes 
to numbers.


http://www2.ignatius.edu/faculty/turner/arabic/anumbers.htm

I agree that it's application specific.  The HTML/Perl script ought to 
convert to Western numerals.


Joe

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

Re: [SQL] UTF8 encoding and non-text data types

[Joe]

Tom Lane wrote:

"Medi Montaseri" <[EMAIL PROTECTED]> writes:
  

insert into t1 (c1, cost) values ('tewt', 1234)
this works find
insert into t1 (c1, cost) values ('شد',
۱۲۳۴)
 DBD::Pg::db do failed: ERROR:  syntax error at or near ";" at character 59,



Well, you've got two problems there.  The first and biggest is that
&#NNN; is an HTML notation, not a SQL notation; no SQL database is going
to think that that string in its input is a representation of a single
Unicode character.  The other problem is that even if this did happen,
code points 1777 and nearby are not digits; they're something or other
in Arabic, apparently.
  
Precisely. 1777 through 1780 decimal equate to code points U+06F1 
through U+06F4, which correspond to the Arabic numerals 1 through 4.


Joe

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

Re: [SQL] Argentinian timezone change at the last moment. How to change pgsql tz db?

[Joe]

Scott Marlowe wrote:

I've been reading up on zic and wondering if it's a reasonable thing
to try and update the pg tz db to include the new argentinian DST
change.  Where is the tz info stored in postgres?  In the catalog?
  
Typically in /usr/share/postgresql/timezone or maybe 
/usr/local/share/postgresql/timezone.


Joe

---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

   http://www.postgresql.org/about/donate

Re: [SQL] [GENERAL] Encrypted column

[Joe Conway]

Marko Kreen wrote:

On 6/5/07, Brian Mathis <[EMAIL PROTECTED]> wrote:

pgcrypto also supports md5, so I'm not sure what you're referring to
here.


digest(psw, 'md5') vs. crypt(psw, gen_salt('md5'))


As I already mentioned, *salting* before you hash is a very
important step.  I'm not sure if you saw that in my post.  Without a
salt, it's trivial to generate a list of all combinations of md5'd
strings and their results, up to reasonable lengths.  Then it would be
very simple to look up each hash and get the original text.  With a
salt, you need to generate all possible md5s for all possible salts --
a much harder task.


I dont think its practical method tho'.  Rather, when doing
dictionary-based or bruteforce attack, then if hashes do not
have salts you attack them all at once.

But if they have salts then for each word you try you need to
hash it for each salt.  Which basically gives the effect that
each hash needs to be attacked separately.

In case of attacking one hash the salt does not matter,
only the algorithm counts then.  In that case as i said,
event salted md5 is weaker than des-crypt.


The best method as far as I understand it is HMAC 
(http://www.faqs.org/rfcs/rfc2104.html).


It has some significant cryptanalysis behind it to ensure it does not 
leak information that would compromise the password. Even MD5 and SHA1, 
which have been shown to have certain weaknesses, are not at issue when 
used with HMAC (see, for example, section 3.1.1 of 
http://www.apps.ietf.org/rfc/rfc4835.html)


The way you would use HMAC is:
1. generate a random token, whatever length you want (the salt)
2. use HMAC (implemented with either md5 or sha1 or something newer) to
   hash the salt with the password
3. store the salt and the resulting HMAC hash
4. on login, calculate the HMAC of the token using the provide password,
   and compare to the stored hash

pgcrypto appears to support HMAC. It is also relatively easy to 
implement on top of the built in md5 function if you'd rather not 
install pgcrypto. And I'm sure there are HMAC functions available that 
could be used in PL/Perl and/or PL/Python.


Joe

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

Re: [SQL] We all are looped on Internet: request + transport = invariant

[Joe]

Hi Dmitry,

On Wed, 2007-05-02 at 08:05 +0300, Dmitry Turin wrote:
> J> The average man or woman on the street
> 
> For what you say about street ?
> Average people, which you can meet on street, make physical job.

That is an American colloquialism to refer to just about anyone,
regardless of what kind of work they do.  The point is that --using the
Pareto principle-- 80% (probably much more) of the people don't know SQL
or are fluent in other programming languages, and they don't want to be
bothered with *any* of it except to use the products and services that
are made possible through them.

According to the U.S. Bureau of Labor Statistics (BLS) there were
455,000 programming jobs in 2004.  Even if you raise that by an order of
magnitude you're still talking about less than 2% of the U.S.
population.  The BLS estimates there were 16,000 physicists/astronomers
and 77, biological scientists.  So the software/programming and
scientist populations may be roughly comparable.  While the ratios may
be better in some other countries, I doubt that they're much off.

The bottom line is: the markets for PHP/etc. and TML are not too large,
but you seem to be having a hard time convincing those of us who've
taken even a mild interest in TML that it's really needed or is a better
solution than what exists today.

Joe


---(end of broadcast)---
TIP 1: 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

Re: [SQL] We all are looped on Internet: request + transport = invariant

[Joe]

Dmitry,

On Thu, 2007-04-26 at 11:33 +0300, Dmitry Turin wrote:
> Joe, i speak not about you, but about statistics.

Do you actually have statistics of how many people in the general
population have learned SQL?  And furthermore, how many of those people
didn't already know or didn't want to bother learning another
computer-related language?

> J> there are others on this list and elsewhere that have done so
> 
> Second question: why?
> For what people must learn php, etc,
> if they need only output data from database.

If people only need to examine or visualize data, they can use any
number of "user friendly" tools:  PgAdmin, Excel with ODBC, etc. (and
these may not be the best examples).  However, someone will still have
to learn SQL and C, PHP, Python, etc., to create an application to input
and maintain the data.

> >others may require the assistance of a
> J> technical specialist or a full-time programming team, but what's wrong
> J> with that?
> 
> expenses of money, time, nerve to explain task to other man

I believe your worldview is affected by the people you interact with.
The average man or woman on the street doesn't want to know about SQL,
relational databases, third normal form or, with all due respect, TML.
If they have Internet access, they want to enter a query string into
something like Google or Amazon and look at the results.  Do you think
if Amazon gave raw read access to their underlying database, many people
would jump to query it with SQL, TML or any computer language?  And even
those who did, when they found a book of their choice they would still
need APPLICATION code to enter their order.

OTOH, I think you're dealing with a select group of scientists who have
these data and they would love to "mine" it and analyze it to the nth
degree but they don't want to jump through (too many) hoops to do so.
And maybe TML is just what they need.

And there are others (business people, for example) who also need to
analyze the data they have and they don't mind paying for programmers,
DBAs, etc. (even if sometimes it may be tough for them explaining *what*
they want).  For those, TML may or may not be a solution.  The
marketplace will decide.

Joe


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

Re: [SQL] We all are looped on Internet: request + transport = invariant

[Joe]

Hi Dmitry,

On Wed, 2007-04-25 at 10:47 +0300, Dmitry Turin wrote:
> J> And there's nothing wrong with Perl, PHP, Python and the myriad
> J> interface languages.
> 
> I said many times, what is wrong:
> applied users can not join sql and perl, can not use libraries,
> and can not adjust web-server.

I strongly disagree.  I have not taken any formal courses on PHP, HTML,
Apache or Python, and I only took a couple of week-long courses on SQL
ages ago (Perl I don't care for).  Yet I've learned enough on my own to
"join" them and use their libraries and put up a website.  And I believe
there are others on this list and elsewhere that have done so, to
varying degrees.  And yet others may require the assistance of a
technical specialist or a full-time programming team, but what's wrong
with that?

> J> thousands of users may agree and converge on those choices.
> 
> 1. Not users, but programmers.
> 2. Needs are produced also, as goods and capital goods.
>  Karl Marks
>For example, look at yourself.

We are on diametrically opposed sides of that argument, but it's
off-topic, so I'll leave it alone.

Joe


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

Re: [SQL] We all are looped on Internet: request + transport = invariant

[Joe]

Hi Dmitry,

On Tue, 2007-04-24 at 15:31 +0300, Dmitry Turin wrote:
> J> How do I see employees in just one department?
> 
> department[id="1"].employee >>;
> 
> or
> 
> department[name="Technical"].employee >>;

How is that any different or better than a standard SQL SELECT (or to
use another context, a Tutorial D statement)?

> J> How do I see a single employee?
> 
> employee[id="31"] >>;
> 
> or
> 
> employee[name="Tomson"] >>;
>-- maybe several employee with surname Tomson

What if I want to see specific columns of the employee row or see the
name of the department that Tomson works in or the name of Tomson's
manager?

> J> How do I actually retrieve SUM(salary)
> 
> Declare field "sum" as not fictional.
> 
> You can always declare field "sum" as not fictional and
> put sign "#" before field to prevent output of field.

This "fictional" attribute of yours implies a change to SQL DDL.

> J> GROUP BY department?
> 
> It is always "group by", because it is field of record "department"

That is only in *your* view of the data.  What if someone comes along
and wants to do SELECT name, SUM(salary) FROM employee GROUP BY name?
Or GROUP BY date_trunc('year', hire_date)?

The point is that SQL has been refined (although some may disagree :-)
over the past quarter century to answer this and many other questions
and I don't see how TML improves on that (for an enhanced answer to SQL,
take a look at Tutorial D).  And XML is *not* the only answer to viewing
or even "transporting" data.  Some people prefer a tabular
representation, others may prefer a hierarchical view, and yet others
want to see a chart (and even the same person may prefer a different
view for various purposes or at various times).

And there's nothing wrong with Perl, PHP, Python and the myriad
interface languages.  That's what "free" software is all about.  The
POSTGRES UCB project had a language called PostQUEL, which may have been
technically superior to SQL, but market forces (unfortunately not all
"free") pushed Postgres95 and then PostgreSQL to adopt the latter in
preference to PostQUEL.  Maybe one day we'll have one (or two) interface
languages to PostgreSQL that 80% of the users will use, but it will not
be because you or I or even the PG Global Dev Group leaders say it's
"better" or "it's necessary", but because thousands of users may agree
and converge on those choices.

Joe


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

   http://archives.postgresql.org

Re: [SQL] We all are looped on Internet: request + transport = invariant

[Joe]

Hi Dmitry,

On Mon, 2007-04-23 at 11:20 +0300, Dmitry Turin wrote:
> >I would change your examples to use less abstract
> > data, like department/employee,  customer/product/order/order_line
> 
> This will not help.
> To my mind, forum of real database is place,

"What we got here is ... failure to communicate."

I may be wrong, but I suspect most of the people in this forum take a
look at

create table a (
  id   num  primary key;
  data float;
);
create table b (
  id   num  primary key;
  ref  num  references a(id);
  data float;
);
create table c (
  id   num  primary key;
  link num  references b(id);
  data float;
);

or even

create table a (
  id   integer primary key,
  data float);
create table b (
  id   integer primary key,
  ref  integer references a(id),
  data float);
create table c (
  id   integer primary key,
  link integer references b(id),
  data float);

and your attempts to derive XML from them and ask questions like "how
will this help me in my day-to-day job" and "what do a, b and c
represent or are analogous to?" and all they'll see is a hierarchy of
some sort (as evident by several of the responses) and they'll dismiss
it because they have to deal with more complex relationships and the
relational model allows them to express such connections.

OTOH, if you were to take a real-life example, and show you can generate
XML from your proposed TML, I contend that then you'd find more people
receptive to your ideas or at least able to criticize them from more
concrete viewpoints.

Take a look for example at Philip Greenspun's "SQL for Web Nerds".  It's
dated and its examples are based on Oracle syntax, but it's based on a
real web application.  Show us what TML can do for the users, bboard and
classified_ads tables
(http://philip.greenspun.com/sql/data-modeling.html), and how it can
answer the simple and complex queries in the next two chapters (or as I
said, pick a real-life example of your own) and then maybe we'll have a
more fruitful conversation.

Joe


---(end of broadcast)---
TIP 1: 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

Re: [SQL] We all are looped on Internet: request + transport = invariant

[Joe]

Hi Richard,

On Fri, 2007-04-20 at 13:50 +0100, Richard Huxton wrote:
> OK, but I'm still not seeing how this avoids me having to use 
> PHP/Perl/etc anyway. I mean, I'm going to need some application logic at 
> some point, in which case who cares whether the RDBMS has this specific 
> layout as a format. I can see how it might be useful as a library, but 
> then there are libraries that provide plenty of XML formatting.

But if TML catches on so that "even a caveman can do it" *, i.e., query
a database from just a browser, then you and I (and most everyone on
this list) will be out of a job and PHP/Perl/etc. will be relegated to
the dustbin of programming languages.  [Sorry, couldn't resist :-) ]

Joe

* see http://en.wikipedia.org/wiki/Geico


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

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

Re: [SQL] We all are looped on Internet: request + transport = invariant

[Joe]

Hi Richard,

On Fri, 2007-04-20 at 12:14 +0100, Richard Huxton wrote:
> I'm not sure that anyone is clear why you just don't write this as a 
> simple php/perl/ruby/whatever script? There are libraries that will 
> output XML for you in most of these I'd suspect, and if not it's just an 
> afternoon's work.
> 
> I mean, your application is written in *some* language, isn't it?

It appears that Dmitry wants that an application, written in whatever
language, will issue an SQL query (or maybe a TML "query" such as
"a.b.c") and get back the XML.  He's not concerned about loss of
datatype information because from his perspective the application will
just "present" the XML and similarly he's not concerned about DDL
features because TML is a "transport" mechanism.

I hope I haven't totally misrepresented Dmitry's words, but it seems he
wants to create a universal "database to web" interface language, so
that you and I won't have to deal with the pesky complications of
retrieving data in C with libpq or with PHP, Python and what not
libraries or "adapters", and then have to transform that data for
display to the user.

However, IMHO this appears to be rather simplistic and not completely
grounded in actual experience.  In other words, Dmitry, have you written
a TML parser or a libpq prototype that given a TML "query" produces the
XML and tested it with datatypes other than integer and float.  Or do
you have feedback from actual average users that TML is indeed easier to
use than SQL?

Two other items to think about.  One is that the scheme apparently
requires that tables be defined in a special way, but no indication is
given as to how this will be accomplished or ensured that it is correct.
Second is how does TML handle relational manipulations such as
restriction, projection or aggregation.  It appears TML is primarily for
joins.

Lastly, Dmitry, I think you'll be better off discussing this in
comp.databases.theory.

Joe


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

Re: [SQL] A long-running transaction

[Joe]

Hi Andrew,

On Fri, 2007-04-13 at 07:49 -0400, Andrew Sullivan wrote:
> Because in the general case, you need the intermediate rows.  The
> problem is that you'd have to write a special piece of code to catch
> the case where nobody else can see the row that you're about to
> expire, and that test isn't free.  Therefore, you do it the same way
> any other row gets expired.

Just curious:  is there a way to defeat MVCC?  i.e., if you can lock the
database exclusively, there won't be any readers or writers?

Joe


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

   http://archives.postgresql.org

Re: [ADMIN] [SQL] Urgent help in bit_string data type

[Joe]

Hi skarthi,

On Wed, 2007-04-11 at 16:29 -0700, Karthikeyan Sundaram wrote:
>   The reason why I am asking is, we are building an interface layer
> where all our users will have a view.  They shouldn't know anything
> about how and where the data is stored in the table.  They can be seen
> only by the portal which will use view.
>  
> That's the reason.

I can understand using views to hide data from users, but that was not
what I was asking about.  It seems that you still have not read the page
that we referenced.  Consider the following:

test=> create table test_a (b bit(3));
CREATE TABLE
test=> insert into test_a values (b'001');
INSERT 0 1
test=> insert into test_a values (b'010');
INSERT 0 1
test=> insert into test_a values (b'101');
INSERT 0 1
test=> select * from test_a;
  b  
-
 001
 010
 101
(3 rows)

test=> create or replace view test_vw as
test-> select b::bit(1) as b2, (b<<1)::bit(1) as b1,
test-> (b<<2)::bit(1) as b0 from test_a;
CREATE VIEW
test=> select * from test_vw;
 b2 | b1 | b0 
++
 0  | 0  | 1
 0  | 1  | 0
 1  | 0  | 1
(3 rows)

The view above gives the same results as your original view, but only
uses bit manipulations (and the only counterintuitive part is ::bit(1)
gives you the MSB).  Your view has to convert a bit string to text (or
maybe bytea) for the substring function, then it has to convert the text
to int because of your explicit cast, and finally it has to convert back
to text for the to_number function.  The result of to_number is numeric
and you're trying to cast it to bit, which is what the ERROR was telling
you can't do.

Joe


---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Re: [ADMIN] [SQL] Urgent help in bit_string data type

[Joe]

Hi skarthi,

On Wed, 2007-04-11 at 15:01 -0700, Karthikeyan Sundaram wrote:
> create table test_a (b bit(3));
> 
> create view test_vw (b1, b2, b3)
> as select 
> to_number(substring(b,1,1)::int,'9') as b1,
> to_number(substring(b,2,1)::int,'9') as b2,
> to_number(substring(b,3,1)::int,'9') as b3 from test_a;
> 
> create or replace rule test_a_ins as on insert to test_vw
> do instead
> insert into test_a (b) values (COALESCE(new.b1::bit,'1')||
> COALESCE(new.b2::bit,'0')||COALESCE(new.b3::bit,'0')::bit);
> 
> ERROR:  cannot cast type numeric to bit
>  
> How will I resolve this?

*My* question is why are you doing such convoluted conversions, from bit
string to text, then to int, etc.?  It seems to me like you want to
manipulate bits and if that's the case, you should be using the bit
string operators, as someone pointed out a couple of days ago.  In case
you haven't looked at them, please see:

http://www.postgresql.org/docs/8.2/static/functions-bitstring.html

Joe


---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Re: [SQL] Urgent help in bit_string data type

[Joe]

Hi skarthi,

On Wed, 2007-04-11 at 13:30 -0700, Karthikeyan Sundaram wrote:
> insert into test_a values (to_char(1,'9'));
>  
> ERROR:  column "b" is of type bit but expression is of type
> text
> HINT:  You will need to rewrite or cast the expression.

As suggested by the error, you should use a cast, e.g.,

insert into test_a values 9::bit(3);

This will result in binary '001' being inserted because you need 4 bits
to represent decimal 9.

Joe


---(end of broadcast)---
TIP 1: 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

Re: [SQL] exception handling in postgres plpgsql

[Joe]

Hi,

On Tue, 2007-04-03 at 15:35 -0700, Karthikeyan Sundaram wrote:
>   exception
>when NO_DATA_FOUND
>then
>   return 100;
> end;
> $$
> language 'plpgsql';
>  
> When I compile, I am getting an error message 
> ERROR:  unrecognized exception condition "no_data_found"
> CONTEXT:  compile of PL/pgSQL function "audio_format_func" near line
> 15

The constant is no_data.  See
http://www.postgresql.org/docs/8.2/static/errcodes-appendix.html

Joe


---(end of broadcast)---
TIP 1: 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

Re: [SQL] Dummy question

[Joe]

Hi Ezequias,

On Thu, 2007-03-22 at 16:43 -0300, Ezequias R. da Rocha wrote:
> I must use a select * from some table but i must do a join and it must 
> be in the were clause.
> 
> I can't put select * from tabel1 as tb1, table2 as tb2
> where tb2.id = 2
> and tb1.fk_tb2ID = tb2.id

Try 

select tb1.* from tabel1 as tb1, table2 as tb2
where tb2.id = 2
and tb1.fk_tb2ID = tb2.id;

Joe


---(end of broadcast)---
TIP 1: 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

Re: [SQL] Statistics

[Joe]

On Fri, 2007-03-09 at 14:22 -0300, Ezequias Rodrigues da Rocha wrote:
> Does someone have statistcs from PostgreSQL ? Numbers from the list,
> performance statistics. I must argue with another person the idea of
> do not put Oracle in our organization.
> 
> We are quite well with postgresql and I have no plans to change my
> plataform. 

Allow me to quote from my response to your previous message back in
November:

On Fri, 2006-11-24 at 11:07 -0200, Ezequias Rodrigues da Rocha wrote:
> Does anybody have numbers of PostgreSQL in action ?
> 
> Numbers like the biggest insert in mileseconds, the larger database
etc ?

First, you may want to post this in the GENERAL or in the PERFORMANCE
lists since this isn't really about SQL.

Second, you may want to look at the case studies page:
http://www.postgresql.org/about/casestudies/.

Third, the companies like EnterpriseDB and Pervasive may have some of
what you're looking for since they have to measure themselves against
the competition.

And remember to take any numbers with a large grain of salt, YMMV, etc.

Joe


---(end of broadcast)---
TIP 1: 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

Re: [SQL] pg_dump inquiry

[Joe]

On Thu, 2007-03-01 at 12:30 -0300, Osvaldo Rosario Kussama wrote:
> http://www.postgresql.org/docs/8.2/interactive/app-pgdump.html
> "Multiple tables can be selected by writing multiple -t switches. Also, 
> the table parameter is interpreted as a pattern according to the same 
> rules used by psql's \d commands (see Patterns), so multiple tables can 
> also be selected by writing wildcard characters in the pattern."

But note that this is a new feature in 8.2.  In 8.1 and earlier,
multiple -t switches will only get you the last one specified.

Joe


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

Re: [SQL] Very large IN-clause is slow, but how to rewrite it?

[Joe Conway]

Tom Lane wrote:

Richard Jones <[EMAIL PROTECTED]> writes:

I've been profiling a PG database / mix of applications and found that
one statement which takes a very long time to execute is:


PG 8.2 does better with long IN-lists ... although if the list is so
long as to be fetching a significant fraction of the table, you'll still
have problems.  In that case I'd advise putting the values into a temp
table, ANALYZEing same, and doing "WHERE foo IN (SELECT x FROM tmp_table)".


If 8.2, what about
  ... WHERE foo IN (select x from (values (1$),(2$),...,(N$)) as t(x))
?

It would be interesting to see how that compares performance-wise.

Joe


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

  http://archives.postgresql.org

Re: [SQL] how do I to generate a sequence Range or Set of integer constants

[Joe]

On Fri, 2007-02-23 at 19:25 +0100, Stefan Becker wrote:
> dear SQL friends,
> 
> What I want to do might be done differantly.  Right now I can't
> think of another solution other than a select statement
> 
> I would like to create a sequence range of integer constants.  Join
> this sequence against a ID Range in a database and look for missing
> Id's.   
> 
> Another application for this would be to simply populate a database with
> say 1000.. Records
> 
> Now:  Is there a syntax that allows for the following.
> 
> create table XX (id int);
> insert into XX (select  xx from "1 to 1000" of integers)
> 
> or...
> 
> select IntSeq.MissingValues, x.UniqIntId,x.A,x.B,x.C, 
> from MyDataTable x
> left outer join 
> (
>  select  MissingValues from "1 to 1000" of integers
> ) IntSeq on MissingValues=x.UniqIntId
> 
> 
> I'm hoping that someone has done this and might be able to
> point to some function or methode to do this

Maybe something like this will help:

SELECT id
FROM generate_series(1, (SELECT last_value FROM id_seq)) AS s(id)
EXCEPT
SELECT UniqIntId FROM MyDataTable
ORDER BY id;

The id_seq is the sequence on your ID column, assuming it has one, or
you can replace the (SELECT ... FROM id_seq) by 1000.

Joe


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

   http://archives.postgresql.org

Re: [SQL] simple web search

[Joe]

Hello Louis-David,

On Fri, 2007-02-23 at 17:27 +0100, Louis-David Mitterrand wrote:
> I'm considering implementing a search box on my review web site 
> http://lesculturelles.net and am looking for a simple way to match 
> entered words against several columns on related tables: show.show_name, 
> story.title, person.firtname, person.lastname, etc.
> 
> What is the most elegant way to build a single query to match search 
> words with multiple columns?

You may want to take a look at contrib/tsearch2.

Joe


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

Re: [SQL] COPY FROM query.

[Joe]

Hi Paul,

On Mon, 2007-02-12 at 08:19 +0900, Paul Lambert wrote:
> I'm attempting to copy from a table into a file using a select query 
> inside the copy.
> 
> The following is my command:
> 
> COPY (SELECT DISTINCT ON (dealer_id,appraisal_id) * FROM 
> appraisals_temp) TO 'c:\autodrs_appraisal_new.txt' WITH DELIMITER AS '^' 
> CSV HEADER;
> 
> I get the following returned:
> 
> WARNING:  nonstandard use of escape in a string literal
> LINE 1: ...ealer_id,appraisal_id) * FROM appraisals_temp) TO 'C:\autodr...
>   ^
> HINT:  Use the escape string syntax for escapes, e.g., E'\r\n'.
> 
> ERROR: relative path not allowed for COPY to file
> SQL state: 42602
> 
> 
> (The caret character is pointing to the M in FROM)

I believe that on Windows you need to use double backslashes, i.e., 'c:\
\autodrs_appraisal_new.txt', although the regular slash may also work,
i.e., 'c:/autodrs_appraisal_new.txt'.

Joe


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

Re: [SQL] huge disparities in =/IN/BETWEEN performance

[Joe]

Hi Tom,

On Thu, 2007-02-08 at 23:24 -0500, Tom Lane wrote:
> Certainly, but your other concerns don't follow from that.  The issue at
> hand here is whether it's worth expending cycles on every query to try
> to detect a situation that only holds for a few.

Those where George's concerns, but I was interested in knowing whether
the planner transforms a query in any way to avoid, let's say, useless
execution.  George didn't provide the inside of his view, but it's
possible that my earlier example could be rephrased as follows:

create view v_foo as select * from tab where x < 5;
select * from v_foo where x = 10;

Presumably the planner has to transform the SELECT into

select * from tab where x < 5 and x = 10;

or something analogous.  This is a simple example, but with complicated
views or even joins and aggregates, the "useless execution" may not be
that obvious to the "naked eye" but it would be to a boolean logic
analyzer.

As to whether these query instances represent few or are typical is
arguable, and will depend on the type of application, level of knowledge
among users, and what kind of interfaces are used to enter or generate
the queries.

Joe


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

Re: [SQL] huge disparities in =/IN/BETWEEN performance

[Joe]

Hi Tom,

On Thu, 2007-02-08 at 22:50 -0500, Tom Lane wrote:
> There's a datatype abstraction issue involved: what does it take to
> prove that "x >= 10 AND x <= 10" is equivalent to "x = 10"?  This
> requires a nontrivial amount of knowledge about the operators involved.
> We could probably do it for operators appearing in a btree operator
> class, but as Alvaro says, it'd be cycles wasted for non-dumb queries.

Are you saying the planner is datatype-agnostic and can't tell that x
is, say, as in the example above, an INTEGER and therefore cannot
transform one expression into another?  What about "x = 10 AND x < 5"?
Can't it reduce that to FALSE?

Joe


---(end of broadcast)---
TIP 1: 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

Re: [SQL] Open a Transaction

[Joe]

Hi Ezequias,

On Thu, 2007-02-08 at 14:50 -0200, Ezequias Rodrigues da Rocha wrote:
> Hi list,
> 
> Could someone tell me what is wrong on this statement ?
> 
> Start Transaction

The above should read 

begin;

> delete from base.something
> where
> id in(
> 41503,
> 41504,
> 41505,
> 41506,
> 41507,
> 41508,
> 41509,
> 41510,
> 41511,
> 41512,
> 41513,
> 41514,
> 41515,
> 41516,
> 41517,
> 41518,
> 41519,
> 41520,
> 41521,
> 41522,
> 41523,
> 41524,
> 41525,
> 41526,
> 41527,
> 41528,

Remove the extra comma.

> )
> end;

Remove 'end'.

> Commit;

Joe


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

Re: [SQL] Log, Logs and more Logs

[Joe]

On Wed, 2007-01-31 at 10:12 -0200, Ezequias Rodrigues da Rocha wrote:
> I restart the server but the pg_log folder doesn't appears.
> 
> My log session is like this on postgresql.conf:
> 
> # These are only used if redirect_stderr is on:

Did you look at the setting of redirect_stderr, and the various logging
settings just above it?

Joe


---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Re: [SQL] Need to subtract values between previous and current row

[Joe]

On Fri, 2006-12-15 at 18:27 +0600, Partha Guha Roy wrote:
> I have a table that contains data like this:
> 
> ID   ATIME (MM/dd/)
> ==  
> 110/12/2006
> 210/14/2006
> 310/18/2006
> 410/22/2006 
> 510/30/2006
> 
> Now I need a query that will subtract atime of row 1 from row 2, row2
> from row3  and so on...
> 
> Can anyone please help?

How about something like this

select x.id, x.atime, x.atime - y.atime as diff
from yourtable x, yourtable y
where x.id + 1 = y.id;

Joe


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

Re: [SQL] Numbers

[Joe]

On Fri, 2006-11-24 at 11:07 -0200, Ezequias Rodrigues da Rocha wrote:
> Does anybody have numbers of PostgreSQL in action ?
> 
> Numbers like the biggest insert in mileseconds, the larger database etc ?

First, you may want to post this in the GENERAL or in the PERFORMANCE
lists since this isn't really about SQL.

Second, you may want to look at the case studies page:
http://www.postgresql.org/about/casestudies/.

Third, the companies like EnterpriseDB and Pervasive may have some of
what you're looking for since they have to measure themselves against
the competition.

And remember to take any numbers with a large grain of salt, YMMV, etc.

Joe


---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Re: [SQL] Question about time

[Joe]

On Thu, 2006-11-16 at 10:14 -0600, Judith wrote:
> Hi everyone I'm doing a query like this:
> 
> SELECT CAST(fecha_hora_factura as time) FROM nota_venta
> 
> and I get:
> 
> 14:16:52.824395
> 
> the field is timestamp type... I just want the HOUR:MINUTE:SECOND
> 
> the question is how I drop the millisecond??

Take a look at date_trunc() under Date/Time Functions and Operators.

Joe


---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Re: [SQL] psql -F

[Joe Conway]

T E Schmitz wrote:

Alvaro Herrera wrote:


T E Schmitz wrote:


I have written a shell script to export data:

psql -A -t -U $DBUSER -d $DB -c "$QUERY" -F ' '

Currently, I am using spaces as field separator but what I really 
want is tabs.


How can I specify a TAB character with the -F option?


Sorry for having gone off-topic. I was just hoping something like \t 
could be passed, too.




Try:
   psql -A -t -U $DBUSER -d $DB -c "$QUERY" -F $'\t'

see:
   man bash

HTH,

Joe

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

Re: [SQL] Between and miliseconds (timestamps)

[Joe]

Hi Ezequias,

On Fri, 2006-11-10 at 16:34 -0200, Ezequias Rodrigues da Rocha wrote:
> Ok thank you very much, but the suggestion (SQL:when field =
> '2006-09-06'::date) does not retrieve any row. It does not retrive any
> error too, but the suggestion does not applied to my requisition. 

I think you want to cast the field, not the constant, e.g.,

testdb=> select * from t2 where date(tm) = '2006-9-6';
  tm   
---
 2006-09-06 00:00:01-04
 2006-09-06 23:59:59.99-04
(2 rows)

Joe


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

Re: [SQL] show privileges

[Joe]

On Mon, 2006-11-06 at 17:01 -0600, Aaron Bono wrote:
> On 11/2/06, Rares Vernica <[EMAIL PROTECTED]> wrote:
> Hi,
> 
> How can I view the privileges that an user or a role has?
> 
> Or what is the equivalent of "show privileges" from MySQL?
> 
>  
> select * from pg_user;
> 
> Hey guys, this comes up every so often.  Could some kind of syntax be
> added, at least to the psql tool, to get this kind of information.  It
> would be really handy for administrators.  Also, if it were on the
> documentation under the GRANT privileges section that would help
> immensely.  I always have to hunt this down when I need it. 

You mean something like \du at the psql prompt?

Joe





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

   http://archives.postgresql.org

Re: [SQL] Case Preservation disregarding case sensitivity?

[Joe]

Hi Beau,

On Fri, 2006-10-27 at 16:23 -0700, beau hargis wrote:
> I am hoping that there is an easy way to obtain case-preservation with 
> case-insensitivity, or at the very least, case-preservation and complete 
> case-sensitivity, or case-preservation and a consistant case-conversion 
> strategy. 
> 
> The case of the column names need to be preserved because that is the way the 
> schema is designed and most importantly (VERY, VERY IMPORTANT), column names 
> are used in apps as hash values, or as named references which are case 
> sensitive and as such need to be delivered to the client in exactly in the 
> manner specified at the time of table creation.
> 
> Again, I am looking for a way (magic, patches, whiskey, etc) that will give 
> me 
> case-preservation with EITHER case-sensitivity OR case-insensitivity, but not 
> both as I am seeing.
> 
> Thanks in advance. I am hoping to find a solution to this so I can actually 
> convert one of our databases to use Postgres. And I can say that little 
> issues like this are precisely why Postgres was never used in this 
> organization before, even though several of the other database developers 
> like the features, stability and performance of Postgres.

I went through the same issue in my conversion from MySQL to Postgres
and (since I had a small application) I ended up changing up all my
tables and columns "UserProfile" to user_profile.

I'm afraid however, that it's MySQL that is the odd man out.  I haven't
researched this completely but I believe PG follows either the FIPS-127
or SQL-92 standard with respect to what are called "delimited
identifiers".  Basically, this says if you want case sensitivity in
identifier names, you have to use double quotes wherever you refer to
the identifier.  Without the double quotes, the SQL implementor can
either use UPPERCASE (as I believe Oracle and DB2 do) or lowercase (as
PG does) when it displays those identifiers.

Joe


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

Re: [SQL] Is this a bug? Deleting a column deletes the constraint.

[Joe]

On Thu, 2006-10-12 at 01:25 -0400, Tom Lane wrote:
> It does seem like this is wrong, in view of SQL92's statement about
> ALTER TABLE DROP COLUMN:
> 
>  4) If RESTRICT is specified, then C shall not be referenced in
> the  of any view descriptor or in the  condition> of any constraint descriptor other than a table con-
> straint descriptor that contains references to no other column
> and that is included in the table descriptor of T.
> 
> IOW we should only allow unique constraints to be auto-dropped if
> they reference just the one single column.  Ick.

I didn't realize before that you can also drop all columns, leaving a
table without *any* columns.  Is that a SQL92 "feature"?

Joe


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

   http://archives.postgresql.org

Re: [SQL] Evaluation of if conditions

[Joe]

Daniel CAUNE wrote:

Hi,

How does the IF statement evaluate conditions?  Does it evaluate conditions
following their declaration order from left to right?  In case of
or-conditions, does the IF statement stop evaluating conditions whenever a
first or-condition is true?
  

Please see 4.2.12. Expression Evaluation Rules of the manual:

The order of evaluation of subexpressions is not defined. In particular, 
the inputs of an operator or function are not necessarily evaluated 
left-to-right or in any other fixed order.


There's more examples there too.

Joe

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

  http://archives.postgresql.org

Re: [SQL] ERROR: SELECT query has no destination for result data

[Joe]

Ezequias Rodrigues da Rocha wrote:

Hi list,

I have a function like this:

Create OR REPLACE Function base.inserirPontos(char(1), varchar(255), 
numeric(12,2), int8, int8, int8 ) returns int4 as

$$
declare
Operacao alias for $1;
Numero_nota alias for $2;
Valor_nota alias for $3;
PontoVenda_Emissor alias for $4;
Cardpass alias for $5;
Cx_Id alias for $6;

begin

-- Validando parâmetros passados na função

  if Operacao <> 'C' then
 return 1;
   else
 select count(id) as numRegistros from base.emissor_ponto_venda 
where id = PontoVenda_Emissor;

You haven't declared numRegistros.

Joe

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

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

Re: [SQL] Data Entry and Query forms

[Joe]

Aaron Bono wrote:
Are there any web based management tools for PostgreSQL (like Mysql PHP 
Admin except for PostgreSQL)?  I thought I saw a post sometime back 
about one but don't remember the name.


Yes, that's phpPgAdmin (http://phppgadmin.com).

Joe

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

Re: Fwd: [SQL] Start up question about triggers

[Joe]

Andrew Sullivan wrote:

On Tue, Jun 27, 2006 at 11:16:17AM +0300, Forums @ Existanze wrote:

I looked into slony, I have a question though, how would I go about
controlling slony via a jdbc driver? See this whole problem has arisen
because for some reason my client wants to keep to separate databases in two
separate locations with the same data. So he would call this partiall backup
function via a GUI client we provide, and with this information he would go
to the same GUI client in this other location and import this partiall
backup. 


It is totally normal to want to keep two databases in two locations:
that's a matter of safety.  Slony does it automatically, as long as
the daemon is running.  No need to control it.


But with file-based log shipping (see 
http://linuxfinances.info/info/logshipping.html) one could write a Java 
app to control when the updates are applied.


Joe

---(end of broadcast)---
TIP 1: 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

Re: [SQL] avg(interval)

[Joe]

Erik Jones wrote:
Well, the query is working ok numerically, but should the answer really 
be reported as 4 days and 33 hours?


Well, the original poster didn't provide the table schema or PG version, 
but on PG 8.0.3 both with intervals or with differences between 
timestamps, the query appears to work OK:


test=> select * from x;
t
-
 7 days 22:24:00
 9 days 22:21:00
 23:21:00
 4 days 22:47:00
 3 days 06:05:00
(5 rows)

test=> select avg(t) from x;
   avg
-
 5 days 09:47:36
(1 row)

test=> select * from x2;
  t
-
 2006-06-07 22:24:00
 2006-06-09 22:21:00
 2006-05-31 23:21:00
 2006-06-04 22:47:00
 2006-06-03 06:05:00
(5 rows)

test=> select avg(t - '2006-5-31 0:0'::timestamp) from x2;
   avg
-
 5 days 09:47:36
(1 row)

Joe

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

Re: [SQL] avg(interval)

[Joe]

Tom Lane wrote:

"Jeremiah Elliott" <[EMAIL PROTECTED]> writes:

however if i don't average them here is what i get:
"7 days 22:24:50.62311";"*2420"
"9 days 22:21:02.683393";"*2420"
"23:21:35.458459";"*2420"
"4 days 22:47:41.749756";"*2420"
"3 days 06:05:59.456947";"*2420"



which should average to just over nine days -


Uh ... how do you arrive at that conclusion?  I haven't done the math,
but by eyeball an average of four-something days doesn't look out of
line for those values.


It seems he's calculating (7 + 9 + 23 + 4 + 3) / 5 ...

Joe

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

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

Re: [SQL] Repetitive code

[Joe]

Aaron Bono wrote:
I haven't stared at your query as long as you have so I may have missed 
something but it looks like in all the selects you are combining the 
first column in the select is the column you filter on.  So the the 
outer query doesn't have to know wiether it is a new or changed row:


SELECT * FROM (
SELECT created as my_date, topic_id, 0, 0, 0, 0 FROM topic
WHERE page_type IN (1, 2)
  UNION
[snip]
SELECT e.updated as my_date, e.topic_id, 1, 1, entry_id, rel_type
FROM topic_entry e, topic t
WHERE e.topic_id = t.topic_id
  AND date_trunc('day', e.updated) != e.created
  AND page_type IN (1, 2)
) my_union
where my_union.my_date >= $dt


Thanks Aaron.  That does look like a great solution, overlooked since 
I'm not that familiar with SELECTs in the FROM clause.  It may even make 
it possible to discard the interim table and do the web page/RSS feed 
directly from the view.


I would almost be tempted to create a view for each small query and name 
them something meaningful and then another view that does the union.  It 
would make the queries easier to understand at least (self documented).


That sounds like a good idea too because schema changes would be 
somewhat insulated by the layered views.


Best regards,

Joe

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

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

Re: [SQL] Repetitive code

[Joe]

Aaron Bono wrote:
Each of your queries has the filter xxx >= $dt where the xxx is the 
first column in each select.  You could simplify the query by turning 
the unioned selects into a sub-query and then putting the $dt filter in 
the outer query.


It would probably have to be two subqueries unless I can find a way to 
merge the differences between new and changed rows.


I don't know if this will cause performance problems though.  If 
PostgreSQL completes the inner query before filtering by your $dt you 
may be better off leaving the $dt filters where they are.


The query is only run a few times a week so performance is largely not a 
concern.  I'm trying to simplify it to make adding tables less 
cumbersome (as a separate effort, the schema may be modified to 
normalize it, e.g., topic joins to entry via subject_id and actor_id and 
a subject and actor can also appear in topic_entry's topic_id).


I know Oracle has materialized views.  Does PostgreSQL also have 
materialized views?  If so, you could get great performance from your 
views AND simplify your SQL.


AFAIK PostgreSQL does not support materialized views but it's 
interesting that you mention that because in essence the query is used 
to materialize a view, i.e., it's part of an INSERT / SELECT into a 
table which is then joined back to the other tables to construct a web 
page as well as an RSS feed.


Joe

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

[SQL] Repetitive code

[Joe]

Hi,

This is prompted by the previous thread on "SQL Technique Question".  I 
have the following query, extracted from a PHP script, where $dt is a 
date provided to the script.


SELECT created, topic_id, 0, 0, 0, 0 FROM topic
WHERE created >= $dt AND page_type IN (1, 2)
  UNION
SELECT updated, topic_id, 1, 0, 0, 0 FROM topic
WHERE date_trunc('day', updated) != created
  AND updated >= $dt AND page_type IN (1, 2)
  UNION
SELECT e.created, subject_id, 0, 1, entry_id, subject_type
FROM entry e, topic
WHERE subject_id = topic_id AND e.created >= $dt
  AND page_type IN (1, 2)
  UNION
SELECT e.created, actor_id, 0, 1, entry_id, actor_type
FROM entry e, topic
WHERE actor_id = topic_id AND e.created >= $dt
  AND page_type IN (1, 2)
  UNION
SELECT e.updated, subject_id, 1, 1, entry_id, subject_type
FROM entry e, topic
WHERE date_trunc('day', e.updated) != e.created
  AND subject_id = topic_id AND e.updated >= $dt
  AND page_type IN (1, 2)
  UNION
SELECT e.updated, actor_id, 1, 1, entry_id, actor_type
FROM entry e, topic
WHERE date_trunc('day', e.updated) != e.created
  AND actor_id = topic_id AND e.updated >= $dt
  AND page_type IN (1, 2)
  UNION
SELECT e.created, e.topic_id, 0, 1, entry_id, rel_type
FROM topic_entry e, topic t
WHERE e.topic_id = t.topic_id AND e.created >= $dt
  AND page_type IN (1, 2)
  UNION
SELECT e.updated, e.topic_id, 1, 1, entry_id, rel_type
FROM topic_entry e, topic t
WHERE e.topic_id = t.topic_id
  AND date_trunc('day', e.updated) != e.created
  AND e.updated >= $dt AND page_type IN (1, 2);

As you can see, there's quite a bit of repetitive code, so the previous 
thread got me to thinking about simplifying it, perhaps through a view, 
perhaps through the use of CASE statements, particularly since I'm about 
to add at least one other table to the mix.


As background, each table has a 'created' date column and an 'updated' 
timestamp column and the purpose of the various selects is to find the 
rows that were created or updated since the given $dt date.  The third 
expression in each select list is an indicator of NEW (0) or CHANGED 
(1).  The fourth item is a code for row type (topic=0, entry=1, but a 
new code is coming).


I've been trying to figure out if simplifying into a view (one or more) 
is indeed possible.  One factoring out that I can see is the "topics of 
interest" restriction (i.e., the join of each secondary table back to 
topic to get only topics whose page_types are 1 or 2).  Another 
redundancy is the "date_trunc('day', updated) != created" which is there 
to avoid selecting "changed" records when they're actually new. 
However, although creating these views may simplify the subqueries it 
doesn't seem there is a way to avoid the eight-way UNION, or is there?


TIA

Joe

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

Re: [SQL] MySQL DB to PostgresSQL DB

[Joe]

Jose Apablaza wrote:

- Is posible to export MySQL DB to PostgreSQL DB?
- Does PostgreSQL DB has tools to import MySQL DB?
- If is posible, How do I need to build the DB in MySQL?, in order to 
have success in the exportation.

- Do someone did it before?, exporting MySQL DB to PostgreSQL DB?
- What kind of risk do we can to have in this process?
- How long can take this process?


Yes, it's doable, but it's not as straighforward as

   mysqldump -someflags mydbname | psql -someotherflags pgdbname

I suggest you start by checking the resources/articles in the MySQL 
section of http://www.postgresql.org/docs/techdocs.3 (which I'm glad to 
say is much better organized and comprehensive than when I had to do 
it).  FWIW, I converted using CSV as the intermediate format, and the 
dates and timestamps were the trickiest, which required the use of 
"staging" tables (with a textual representation of the columns).  An 
interesting side effect was discovering data inconsistencies in the 
MySQL database since as part of the conversion I implemented foreign key 
constraints under PostgreSQL (which were missing in the former).


Joe

---(end of broadcast)---
TIP 1: 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

Re: [SQL] query; check for zero and floats

[Joe Conway]

[EMAIL PROTECTED] wrote:

I'm trying to do a simple query and I'm not sure how to get it to work:

SELECT SUM(x0 + y0 + z0) / SUM(x2 + y2) AS A1, SUM(x1 + y1 + z1) / SUM(x3 + y3)
AS A2
FROM test

Problems:

1. All variables are integers.  When it does the division, it returns an
integer, but I want a float.  (I've tried numerous things to no avail)

2. If SUM(x2 + y2) == 0; there is a divide by zero error.  How do I check and
pass on zeroes?


A bit ugly, but seems to work:

create table t1 (g int, x0 int, y0 int, z0 int, x2 int, y2 int);
insert into t1 values (0, 1,1,1,1,-1);
insert into t1 values (0, 1,1,1,-1,1);
insert into t1 values (1,1,1,1,1,1);
insert into t1 values (1,2,3,4,5,6);

SELECT g, case
   when SUM(x2::float8 + y2::float8) = 0 then
 0
   else
 SUM(x0::float8 + y0::float8 + z0::float8) /
 SUM(x2::float8 + y2::float8)
   end
  AS A1 from t1 group by g;
 g |a1
---+---
 1 | 0.923076923076923
 0 | 0
(2 rows)

HTH,

Joe

---(end of broadcast)---
TIP 1: 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

Re: [SQL] Question about One to Many relationships

[Joe]

Todd Kennedy wrote:

They haven't responded me as of yet. There should be a band associated
with each album -- this is handled in code, but other than that this
is the only relational db way I can think of to do it.


But if a band can have songs in many albums and an album can have songs 
from multiple bands, it's a many-to-many relationship, NOT one-to-many. 
 Short of the full track design suggested by PFC, you'd normally 
implement a many-to-many table as follows:


CREATE TABLE bands_on_album (
band_id integer REFERENCES band (id),
album_id integer REFERENCES albums (id),
PRIMARY KEY (band_id, album_id)
)

This of course precludes the same band being listed twice in a given 
album.  If you do need that info, then you're really asking for "tracks".


Joe


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

[SQL] UPDATE with correlated aggregates

[Joe Abbate]

Hi,

I have a database with one "main" table with three columns that 
self-reference the primary key, plus four other tables that have (in 
total) seven foreign key columns referencing "main".  I want to create a 
table (or view) that summarizes the various reference counts, per row in 
"main".  I have an approach that works, for the much simpler situation 
described below, but wanted feedback on possible improvements or 
simplifications before implementing it on the actual tables.


Here are the simplified "main" table and one of the ancillary tables:

dev=> \d main
 Table "public.main"
 Column |  Type   | Modifiers
+-+---
 id | integer | not null
 parent | integer |
 data   | text|
Indexes:
"main_pkey" PRIMARY KEY, btree (id)

dev=> \d items
 Table "public.items"
 Column |  Type   | Modifiers
+-+---
 id | integer | not null
 main   | integer |
 data   | text|
Indexes:
"items_pkey" PRIMARY KEY, btree (id)

dev=> select * from main;
 id | parent |  data
++
  1 || ABC
  2 |  1 | ABCDEF
  3 |  1 | ABCGHI
  4 || PQR
  5 |  4 | PQRSTU
  6 |  4 | PQRUVW
  7 |  4 | PQRXYZ
(7 rows)

dev=> select * from items;
 id | main | data
+--+--
  1 |2 | asdf
  2 |2 | jkl;
  3 |2 | qwer
  4 |3 | uiop
  5 |3 | m,./
  6 |4 | zxcv
  7 |4 | rtyu
  8 |4 | fghj
  9 |4 | vbnm
 10 |6 | asl;
 11 |7 | qwop
 12 |7 | zx./
(12 rows)

This is the summary table:

dev=> \d summ
 Table "public.summ"
 Column |  Type   | Modifiers
+-+---
 id | integer | not null
 subs   | bigint  |
 items  | bigint  |
Indexes:
"summ_pkey" PRIMARY KEY, btree (id)

I first populate "summ" with 'select id from main'.  Since an UPDATE 
apparently cannot include aggregates, i.e., subs = count(*), I chose to 
create two views as follows:


CREATE VIEW main_summ AS
 SELECT parent AS id, count(*) AS subs
   FROM main
  WHERE parent IS NOT NULL
  GROUP BY parent;
CREATE VIEW items_summ AS
 SELECT items.main AS id, count(*) AS items
   FROM items
  GROUP BY items.main;

Then I update "summ" as follows:

update summ set subs = ms.subs
   from main_summ ms where ms.id = summ.id;
update summ set items = its.items
   from items_summ its where its.id = summ.id;

The end result is:

dev=> select * from summ order by id;
 id | subs | items
+--+---
  1 |2 |
  2 |  | 3
  3 |  | 2
  4 |3 | 4
  5 |  |
  6 |  | 1
  7 |  | 2
(7 rows)

In the real database, I'd probably have to create 10 views and do 10 
updates so any simpler approach would be appreciated.  I played with 
creating an intermediate table that was populated from UNION SELECTs of 
the various GROUP BYs, each with an additional column that coded the 
type of relationship, but couldn't figure out how to create the summary 
since the coded column has to be used to add the count to either the 
"subs" or "items" columns (or subcolumns by type).


Thanks for any suggestions and comments.

Joe


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

Re: [SQL] ARRAY() returning NULL instead of ARRAY[] resp. {}

[Joe Conway]

Tom Lane wrote:

Markus Bertheau =?UTF-8?Q?=E2=98=AD?= <[EMAIL PROTECTED]> writes:


By analogy, array_upper('{}'::TEXT[], 1) should return 0 instead of
NULL.


No, that doesn't follow ... we've traditionally considered '{}' to
denote a zero-dimensional array.  A 1-D array of no elements is
'[1:0]={}', just as Joe shows ... or at least it would be except
for an overenthusiastic error check:

regression=# select '[1:0]={}' :: int[];
ERROR:  upper bound cannot be less than lower bound

I think this should be a legal boundary case.  In general, it should be
possible to form zero-size arrays of any number of dimensions.



I've been playing with exactly this over the weekend. Of course, as 
usual, the devil is in the details. For instance, using the above 
notation, how would I specify a zero-element 1D array starting at a 
lower bound index of 0? The answer following the above pattern would be:


  select '[0:-1]={}'::int[];

You could not use '[0:0]={}'::int[], because that would be a one-element 
array. I propose the following instead:


regression=# select '[1:]={}' :: int[];
 int4
--
 {}
(1 row)

regression=# select array_dims('[1:]={}' :: int[]);
 array_dims

 [1:]
(1 row)

In other words, an missing upper bound indicates zero elements.

Now the next question; what does a 2D zero-element array look like? I 
think this makes sense:


regression=# select '[1:2][1:]={{},{}}'::int[];
 int4
--
 {}
(1 row)

Except (I think) array_out() should probably output something closer to 
the input literal. Any thoughts on this?


Joe

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

Re: [SQL] ARRAY() returning NULL instead of ARRAY[] resp. {}

[Joe Conway]

Bruce Momjian wrote:

Joe Conway wrote:


Any thoughts on how this should be handled for an empty 1D array?


No one responed to this email, so I will try.  Is this the one
dimmentional array you were talking about?

test=> select array_dims('{}'::integer[]);
 array_dims


(1 row)


In this case, what you get is actually a dimensionless array. Literally, 
you get this:


if (nitems == 0)
{
/* Return empty array */
retval = (ArrayType *) palloc0(sizeof(ArrayType));
retval->size = sizeof(ArrayType);
retval->elemtype = element_type;
PG_RETURN_ARRAYTYPE_P(retval);
}

I.e. the array structure is allocated, the size is set (which is 
required since arrays are varlena), and the element type is initialized. 
There is no initialization of ndim, ARR_DIMS(), or ARR_LBOUND().


In this case, since there are no dimensions, array_dims() probably does 
the right thing by returning NULL.



Why is [1:0] wrong to return?



I'm not sure it is wrong -- it just seems a bit strange. The difference 
is that in order to return an empty *one-dimensional* array, ndim, 
ARR_DIMS(), and ARR_LBOUND() are all appropriately set (by the patched 
code). Basically, ndim == 1, ARR_DIMS() is a single element int array (C 
array that is) indicating 0 elements for dimension 1, and ARR_LBOUND() 
is a single element int array indicating a lower bound of 1. This leads 
to the array_dims() return value of [1:0]. The value 1 is unquestionably 
correct for the lower bound index, but what should be reported for the 
upper bound? We can't return [1:1], because that would indicate that we 
have one element.


Joe

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

Re: [SQL] ARRAY() returning NULL instead of ARRAY[] resp. {}

[Joe Conway]

Tom Lane wrote:

I think he's got a good point, actually.  We document the ARRAY-with-
parens-around-a-SELECT syntax as

The resulting one-dimensional array will have an element for
each row in the subquery result, with an element type matching
that of the subquery's output column.

To me, that implies that a subquery result of no rows generates a
one-dimensional array of no elements, not a null array.


OK, looks like I'm outnumbered.

But as far as I know, we have never had a way to produce a 
one-dimensional empty array. Empty arrays thus far have been dimensionless.


Assuming we really want an empty 1D array, I created the attached patch. 
This works fine, but now leaves a few oddities to be dealt with, e.g.:


regression=# select array_dims(array(select 1 where false));
 array_dims

 [1:0]
(1 row)

Any thoughts on how this should be handled for an empty 1D array?


The point Markus is complaining about seems like it should
be easily fixable.


Well, "easily" is a relative term. My Postgres hacking neurons have 
gotten kind of rusty lately -- but then maybe that was your underlying 
point ;-)


Joe

Index: src/backend/executor/nodeSubplan.c
===
RCS file: /cvsroot/pgsql/src/backend/executor/nodeSubplan.c,v
retrieving revision 1.69
diff -c -r1.69 nodeSubplan.c
*** src/backend/executor/nodeSubplan.c	6 May 2005 17:24:54 -	1.69
--- src/backend/executor/nodeSubplan.c	26 May 2005 18:52:16 -
***
*** 215,220 
--- 215,221 
  	ListCell   *pvar;
  	ListCell   *l;
  	ArrayBuildState *astate = NULL;
+ 	Oid			element_type = planstate->ps_ResultTupleSlot->tts_tupleDescriptor->attrs[0]->atttypid;
  
  	/*
  	 * We are probably in a short-lived expression-evaluation context.
***
*** 259,268 
  	 *
  	 * For EXPR_SUBLINK we require the subplan to produce no more than one
  	 * tuple, else an error is raised. For ARRAY_SUBLINK we allow the
! 	 * subplan to produce more than one tuple. In either case, if zero
! 	 * tuples are produced, we return NULL. Assuming we get a tuple, we
! 	 * just use its first column (there can be only one non-junk column in
! 	 * this case).
  	 */
  	result = BoolGetDatum(subLinkType == ALL_SUBLINK);
  	*isNull = false;
--- 260,269 
  	 *
  	 * For EXPR_SUBLINK we require the subplan to produce no more than one
  	 * tuple, else an error is raised. For ARRAY_SUBLINK we allow the
! 	 * subplan to produce more than one tuple. In the former case, if zero
! 	 * tuples are produced, we return NULL. In the latter, we return an
! 	 * empty array. Assuming we get a tuple, we just use its first column
! 	 * (there can be only one non-junk column in this case).
  	 */
  	result = BoolGetDatum(subLinkType == ALL_SUBLINK);
  	*isNull = false;
***
*** 432,458 
  		}
  	}
  
! 	if (!found)
  	{
  		/*
  		 * deal with empty subplan result.	result/isNull were previously
! 		 * initialized correctly for all sublink types except EXPR, ARRAY,
  		 * and MULTIEXPR; for those, return NULL.
  		 */
  		if (subLinkType == EXPR_SUBLINK ||
- 			subLinkType == ARRAY_SUBLINK ||
  			subLinkType == MULTIEXPR_SUBLINK)
  		{
  			result = (Datum) 0;
  			*isNull = true;
  		}
  	}
- 	else if (subLinkType == ARRAY_SUBLINK)
- 	{
- 		Assert(astate != NULL);
- 		/* We return the result in the caller's context */
- 		result = makeArrayResult(astate, oldcontext);
- 	}
  
  	MemoryContextSwitchTo(oldcontext);
  
--- 433,459 
  		}
  	}
  
! 	if (subLinkType == ARRAY_SUBLINK)
! 	{
! 		if (!astate)
! 			astate = initArrayResult(element_type, oldcontext);
! 		/* We return the result in the caller's context */
! 		result = makeArrayResult(astate, oldcontext);
! 	}
! 	else if (!found)
  	{
  		/*
  		 * deal with empty subplan result.	result/isNull were previously
! 		 * initialized correctly for all sublink types except EXPR
  		 * and MULTIEXPR; for those, return NULL.
  		 */
  		if (subLinkType == EXPR_SUBLINK ||
  			subLinkType == MULTIEXPR_SUBLINK)
  		{
  			result = (Datum) 0;
  			*isNull = true;
  		}
  	}
  
  	MemoryContextSwitchTo(oldcontext);
  
***
*** 925,930 
--- 926,932 
  	ListCell   *l;
  	bool		found = false;
  	ArrayBuildState *astate = NULL;
+ 	Oid			element_type = planstate->ps_ResultTupleSlot->tts_tupleDescriptor->attrs[0]->atttypid;
  
  	/*
  	 * Must switch to child query's per-query memory context.
***
*** 1010,1016 
  		}
  	}
  
! 	if (!found)
  	{
  		if (subLinkType == EXISTS_SUBLINK)
  		{
--- 1012,1033 
  		}
  	}
  
! 	if (subLinkType == ARRAY_SUBLINK)
! 	{
! 		/* There can be only one param... */
! 		int			paramid = linitial_int(subplan->setParam);
! 		ParamExecData *prm = &(econtext->ecxt_param_exec_vals[paramid]);
! 
! 		prm->execPlan = NULL;
! 
! 		if (!astate)
! 			astate = initArrayResult(element_type, oldc

Re: [SQL] ARRAY() returning NULL instead of ARRAY[] resp. {}

[Joe Conway]

Markus Bertheau wrote:

why does SELECT ARRAY(SELECT 1 WHERE FALSE) return NULL instead of
ARRAY[] resp. '{}'?



Why would you expect an empty array instead of a NULL? NULL is what 
you'd get for other data types -- for example:


regression=# SELECT (SELECT 1 WHERE FALSE) IS NULL;
 ?column?
--
 t
(1 row)

Joe

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

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

Re: [SQL] PGCrypto: Realworld scenario and advice needed

[Joe Conway]

Moran.Michael wrote:
My initial attack plan was to do the following:
 
1. Call decrypt() with the old-passphrase to decrypt each table's existing
data.
2. Temporarily store the decrypted data in temp tables.
3. Delete all rows of encrypted data from the original tables -- thereby
clearing the tables of all data encrypted with the old passphrase.
4. Call encrypt() with the new passphrase to encrypt all data in the temp
tables -- thereby repopulating the production tables with data encrypted
with the new passphrase.
5. Blow away the temp tables.
 
But this seems like a tedious procedure.
 
Is there any simple way to update ALL existing encrypted data with a new
passphrase, assuming you know the old passphrase and encryption type (i.e.
AES, Blowfish, etc.) without having to go through the 5-step process
mentioned above?
Why not use a single UPDATE command, e.g. something like:
UPDATE tbl
 SET f1 = encrypt(decrypt(f1, 'oldkey', 'aes'), 'newkey', 'aes');
Joe
---(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

Re: [SQL] Calling a table residing in another database from Plpgsql

[Joe Conway]

[EMAIL PROTECTED] wrote:
SELECT * from dblink('host=192.168.0.50 dbname=AK1 user=akteam','Select
userid from user_reg') as t (userid integer);
I am getting an error as "ERROR:  function dblink("unknown", "unknown")
does not exist"
Have you installed the dblink functions into your database? See 
README.dblink.

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

Re: [SQL] Create Calendar

[Joe Conway]

Muhyiddin A.M Hayat wrote:
How to create Calendar using Function/View. 
For example i would like to display date 2004-12-01 to 2004-12-20. 

date
--
2004-12-01 
2004-12-02 
2004-12-03 
2004-12-04 
2004-12-05
..
.. 
2004-12-20 

-- Use in Postgres 7.4.x and earlier.
-- In Postgres 8.0.0 generate_series() is a built-in function
CREATE OR REPLACE FUNCTION generate_series(int, int) RETURNS setof int AS '
 BEGIN
  FOR i IN $1..$2 LOOP
   RETURN NEXT i;
  END LOOP;
  RETURN;
 END;
' LANGUAGE plpgsql;
select '2004/12/01'::date + f1 from generate_series(0, 19) as t(f1);
  ?column?

 2004-12-01
 2004-12-02
 2004-12-03
 2004-12-04
 2004-12-05
 [...]
 2004-12-20
(20 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] PL/pgSQL multidimension (matrix) array in function

[Joe Conway]

Sergio Fantinel wrote:
I found how to use, inside a PL/pgSQL function, a two-dimensions array 
(matrix).
There is a limitation: the number of the 'columns' of the matrix is 
fixed at declaration time (in DECLARE section) and you need to manually 
initialize all the elements in the first 'row' of the matrix.
You should use '{}' to initialize the array to empty. See below for an 
example:

CREATE OR REPLACE FUNCTION testarray (integer, integer) RETURNS SETOF 
integer[] AS'
DECLARE
  n alias for $1;-- number of rows is passed as argument
  i INTEGER;
  j integer;
  k alias for $2;  -- matrix columns number
  a integer[];
begin
  for i in 1..n loop
   a := ''{}'';   -- create empty array
   for j in 1..k loop
 a := a || i;
 return next a;
   end loop;
  end loop;
  return;
end;
'LANGUAGE 'plpgsql';

regression=# select * from testarray(2,3);
 testarray
---
 {1}
 {1,1}
 {1,1,1}
 {2}
 {2,2}
 {2,2,2}
(6 rows)
HTH,
Joe
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] reply to setting

[Joe Conway]

Greg Stark wrote:
Just as a side comment, one trick I found very helpful in my mail filters is
to treat any message with one of my message-ids in the references as a
personal message as far as mail notifications. This way I get notifications
for any message on a thread following a post of my own.
Interesting idea -- thanks!
Joe
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [SQL] array_in: '{}}'::text[]

[Joe Conway]

Markus Bertheau wrote:
Is there a reason the array_in parser accepts additional closing braces
at the end?
oocms=# SELECT '{}}'::text[];
 text
--
 {}
(1 ÑÑ)
Hmmm, I was *about* to say that this is fixed in cvs (and indeed, the 
array_in parser is significantly tightened up compared to previous 
releases), but unfortunately, there is still work to be done :(

regression=# SELECT '{}}'::text[];
 text
--
 {}
(1 row)
regression=# select version();
   version
-
 PostgreSQL 8.0.0beta1 on x86_64-unknown-linux-gnu, compiled by GCC gcc 
(GCC) 3.3.3 20040412 (Red Hat Linux 3.3.3-7)
(1 row)

Look for a fix soon, at a cvs repository near you
Joe
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faqs/FAQ.html

Re: [SQL] SQL Challenge: Arbitrary Cross-tab

[Joe Conway]

Josh Berkus wrote:
This is pretty much exactly how contrib/tablefunc's crosstab (non-hashed
version; crosstab(sourcesql, ncols)) works. If you really need it to be
portable, though, application layer procedural code is likely to be the
easiest and fastest way to go. crosstab just wraps the procedural code
in an SRF for you.
No, you're missing one factor in the spec.   Timekeeper_1 for case_id = 182738 
is not the same timekeeper as Timekeeper_1 for case_id = 217437.  That's why 
traditional crosstab plans don't work.
No, I understood. E.g.
create table authorized_timekeepers (
case_id int,
timekeeper_id text
);
insert into authorized_timekeepers values(213447,'047');
insert into authorized_timekeepers values(132113,'021');
insert into authorized_timekeepers values(132113,'115');
insert into authorized_timekeepers values(132113,'106');
insert into authorized_timekeepers values(14,'106');
insert into authorized_timekeepers values(14,'021');
insert into authorized_timekeepers values(14,'115');
insert into authorized_timekeepers values(14,'108');
insert into authorized_timekeepers values(14,'006');
insert into authorized_timekeepers values(14,'042');
insert into authorized_timekeepers values(14,'142');
insert into authorized_timekeepers values(14,'064');
insert into authorized_timekeepers values(14,'999');
select * from crosstab('select case_id, ''cat'' as cat, timekeeper_id
from authorized_timekeepers order by 1',8)
as t(case_id int, tk1 text, tk2 text, tk3 text, tk4 text, tk5 text, tk6 
text, tk7 text, tk8 text);

 case_id | tk1 | tk2 | tk3 | tk4 | tk5 | tk6 | tk7 | tk8
-+-+-+-+-+-+-+-+-
  132113 | 021 | 115 | 106 | | | | |
  14 | 106 | 021 | 115 | 108 | 006 | 042 | 142 | 064
  213447 | 047 | | | | | | |
(3 rows)
Or even:
select * from crosstab('select case_id, ''cat'' as cat, timekeeper_id
from authorized_timekeepers order by 1',4)
as t(case_id int, tk1 text, tk2 text, tk3 text, tk4 text);
 case_id | tk1 | tk2 | tk3 | tk4
-+-+-+-+-
  132113 | 021 | 115 | 106 |
  14 | 106 | 021 | 115 | 108
  213447 | 047 | | |
(3 rows)
But I know that doesn't help you with portability.
Joe
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faqs/FAQ.html

Re: [SQL] SQL Challenge: Arbitrary Cross-tab

[Joe Conway]

Josh Berkus wrote:
The Problem:  for each "case" there are from zero to eight "timekeepers" 
authorized to work on the "case", out of a pool of 150 "timekeepers".  This 
data is stored vertically:

authorized_timekeepers:
case_id | timekeeper_id
213447  | 047
132113  | 021
132113  | 115
132113  | 106
etc.
But, a client's e-billing application wants to see these timekeepers displayed 
in the following horizontal format:

case_id | tk1   | tk2 | tk3 | tk4 | tk5 | tk6 | tk7 | tk8
213447  | 047 | | | | | | | |
132113  | 021 | 115 | 106 | 034 | 109 | 112 | 087 |
etc.
Order does not matter for timekeepers 1-8.
This is a daunting problem because traditional crosstab solutions do not work; 
timekeepers 1-8 are coming out of a pool of 150.

Can it be done?  Or are we going to build this with a row-by-row procedural 
loop? (to reiterate: I'm not allowed to use a custom aggregate or other 
PostgreSQL "advanced feature")

This is pretty much exactly how contrib/tablefunc's crosstab (non-hashed 
version; crosstab(sourcesql, ncols)) works. If you really need it to be 
portable, though, application layer procedural code is likely to be the 
easiest and fastest way to go. crosstab just wraps the procedural code 
in an SRF for you.

Joe
---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

Re: [SQL] Wierded error in recursive function; debugging ideas?

[Joe Conway]

Josh Berkus wrote:
[2] Wrong datatype for second argument in call to in_array
  SQL: SELECT sf_event_decendants(66645,111)
Are you sure this message isn't coming from some PHP middleware, e.g. 
peardb or something. See:
http://us2.php.net/manual/en/function.in-array.php

Joe
---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

Re: [SQL] reply to setting

[Joe Conway]

Bruno Wolff III wrote:
On Sat, Aug 07, 2004 at 09:33:08 +0530,
  Kenneth Gonsalves <[EMAIL PROTECTED]> wrote:
any reason why the default reply-to on this list should not be set to the 
list? I keep replying to postings only to find later that the reply goes to 
the OP and not to the list. reply-all button results in needless duplication
The duplication is needless. Direct replies very often get to the recipient
 ^
 is *not* needless?
faster than ones sent through the lists. It is also possible that the direct
replies might be handled differently by the recipient (e.g. a filter may put
them in different folders).
This is very true. In fact, I get mildly annoyed when people *don't* 
include the direct reply to me, because I very actively filter/redirect 
my mail. Replies directly to me are pretty much guaranteed to be seen 
quickly, but the ones that go to the list might get lost among the 
hundreds of posts that go into my "postgres" inbox every day. I think 
many other people do something similar.

Recipients that prefer not to get separate copies can indicate that desire
by including an appropiate mail-followup-to header.
Also true.
Joe
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faqs/FAQ.html

Re: [SQL] Wierded error in recursive function; debugging ideas?

[Joe Conway]

Josh Berkus wrote:
I'm seeing this bizarre, unreproducable error in my logs:
[2] Wrong datatype for second argument in call to in_array
  SQL: SELECT sf_event_decendants(66645,111)
I also checked for in_array and it's not a visible built-in function.  Is this 
maybe a PostgreSQL bug?   Version is 7.4.1

There is no in_array() function in Postgres that I'm aware of -- you 
sure that isn't array_in()? The rest of that error message doesn't seem 
to be there in 7.4 either. Can we see the function?

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

Re: [SQL] ERROR: Cross-database references are not implemented

[Joe Conway]

Theo Galanakis wrote:
How do I explicidly create a cross-database reference?
Mind you these databases are on the same server.
In MSSQL you could do that through enterprise manager.
Any help would be appreciated.
You cannot do cross database references per se, but you can use schemas, 
which roughly behave like individual databases do in MSSQL. See:
  http://www.postgresql.org/docs/7.4/static/ddl-schemas.html

If you really need cross-database queries, see contrib/dblink (which 
would also work across servers), but it isn't as flexible as using schemas.

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

Re: [SQL] function expression in FROM may not refer to other relations

[Joe Conway]

Philippe Lang wrote:
But the same query with a parameter returns an error:
select id, usr, code, line1, line2 from tbl, get_lines(code); 
--> ERROR:  function expression in FROM may not refer to other relations
of same query level
This is as expected and required -- you cannot refer to other FROM 
clause relations. I believe SQL99 defines a clause (LATERAL IIRC) that 
would allow this, but it hasn't been implemented yet.

Is there another way to run this query and get:
idusr  code   line1  line2
--
1 one  1  A  B
2 two  2  Z  Z
3 three1  A  B
Whats wrong with just using CASE:
select id, usr, code,
   case when code = 1 then 'A' else 'Z' end as line1,
   case when code = 1 then 'A' else 'Z' end as line2
from tbl;
 id |  usr  | code | line1 | line2
+---+--+---+---
  1 | one   |1 | A | A
  2 | two   |2 | Z     | Z
  3 | three |1 | A | A
(3 rows)
Joe
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] contrib/tablefunc crosstab

[Joe Conway]

Caleb Simonyi-Gindele wrote:
My problem is that the rows are populated from right to left irrespective of
the column. So the $561.99 in vet1 actually belongs to vet2. There are never
any blank cells in the middle or start of a row - they always come at the
end. How do I get the amount for that date into the appropriate column?
You either need to be sure there are no "gaps" in your source rows 
(provide one row for every category for each group, e.g. by doing an 
outer join) or use the hashed version of the function. Search the README 
for:

crosstab(text source_sql, text category_sql)
That is the hashed version that will do what you're looking for.
HTH,
Joe
---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [SQL] Converting a plperlu function to a plpgsql function

[Joe Conway]

Devin Whalen wrote:
First line:
my @active_tables=split(/,/,$tables);
Is there anyway to split a variable like the perl split above?
I'm no perl guru, but in 7.4 I believe this does what you're looking for:
regression=# select string_to_array('1,2,3',',');
 string_to_array
-
 {1,2,3}
(1 row)
Second line:
 if ($r=~/^-([0-9]?)([A-z_]+)/)
 {
my $locid = $1;
my $table = $2;
Not sure about this one. Hopefully someone else can chime in. Maybe a 
little less efficient, but it seems like it would be easy enough to 
parse when true.

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

Re: [SQL] Empty array IS NULL?

[Joe Conway]

Markus Bertheau wrote:
How do I specify an empty array with the standard syntax?
Actually, up until the 7.4 release, the array literal syntax was the 
*only* syntax (i.e. '{...}'). The newer array constructor expression 
(i.e. ARRAY[...]) does not yet support creating empty arrays -- there 
are some non-trivial technical difficulties to be solved. Unfortunately 
that won't change in the upcoming 7.5 either :( -- perhaps for 7.6.

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

Re: [SQL] Empty array IS NULL?

[Joe Conway]

Markus Bertheau wrote:
How do I specify an empty array with the standard syntax?
Actually, up until the 7.4 release, the array literal syntax was the 
*only* syntax (i.e. '{...}'). The newer array constructor expression 
(i.e. ARRAY[...]) does not yet support creating empty arrays -- there 
are some non-trivial technical difficulties to be solved. Unfortunately 
that won't change in the upcoming 7.5 either :( -- perhaps for 7.6.

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] Empty array IS NULL?

[Joe Conway]

Markus Bertheau wrote:
is the empty array representable in PostgreSQL, and is it
distinguishable from NULL?
Yes, and yes.
regression=# select '{}'::int[];
 int4
--
 {}
(1 row)
regression=# select NULL::int[];
 int4
--
(1 row)
Since NULL array elements are not currently supported, attempting to 
construct an array with a NULL element results in NULL, not an empty array.

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

Re: [SQL] pivot-like transformation

[Joe Conway]

Torsten Lange wrote:
Hello,   
I have a table with measurement values and columns like this:   
analyses(id, sample_id, parameter[temperatur...], value, unit[?C...], error)   
   
With PL/PgSQL at the end I want try to perform a pivot-like arrangement of 
these data:   
sample_id|Temperature [?C]|parameter2 [mg/L]|...|parameterN [L/year]   
-++-+---+---   
5|   23.00|0.036|...|  35.1   
Not a direct answer with respect to plpgsql, but for pivot table 
functionality see the contrib/tablefunc function crosstab().

I also found very few sources about handling of arrays and how to fill them up  
with query results... things like this. Does anyone know a more comprehensive  
source?  
Have you looked at the online docs?
http://www.postgresql.org/docs/7.4/static/arrays.html
http://www.postgresql.org/docs/7.4/static/functions-array.html
http://www.postgresql.org/docs/7.4/static/functions-comparisons.html
http://www.postgresql.org/docs/7.4/static/sql-expressions.html#SQL-SYNTAX-ARRAY-CONSTRUCTORS
HTH,
Joe
---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [SQL] Last day of month

[Joe Conway]

Greg Sabino Mullane wrote:
How to find the last sunday/mon/sat of any given month.
 
There is probably a smoother way to do it, but here is a
quick little function to do what you ask. Feed it a date
and a number, where 0 is Sunday, 1 is Monday, etc.
How about this:
regression=# select date_trunc('month', current_date + '1 month'::interval);
 date_trunc
-
 2004-03-01 00:00:00
(1 row)
Joe
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])