Re: [SQL] casting to arrays
Joe Conway <[EMAIL PROTECTED]> writes: > Not possible in current releases, but it will be in 7.4 (about to start beta). > It looks like this: Well there is the int_array_aggregate function in the contrib/intagg directory. It has to be compiled separately, and it has a few quirks (like the arrays are zero-based instead of 1-based) but it works more or less, and it does exactly what you describe. But the 7.4 stuff should be much cleaner and more flexible, so if you don't need it right now you're better off waiting. -- greg ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] Help Me
How to convert charter varying type to integer while retrieving the value using ITL tag [query]?! ___ Click below to experience Sooraj R Barjatya's latest offering 'Main Prem Ki Diwani Hoon' starring Hrithik, Abhishek & Kareena http://www.mpkdh.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] column doesn't get calculated
this must be a problem with my sql, but this one has me stumped. the column: Debit."cumm_dbt_blnce" - Credit."cumm_crd_blnce" in the long sql statement below comes out as the literal: Debit."cumm_dbt_blnce" - Credit."cumm_crd_blnce" rather than as the calculated value. WHY!?!?! this works with simple sql: select a."field1" - b."field2" from (select field1 as "field1" from someTable) as a, (select field2 as "field2" from someOtherTable) as b; but with my more complex sql, it doesn't. it comes out as a literal string. it's almost like postgresql forgot to process this column. select cred_vend_acct_table.num as "num" , cred_vend_acct_table.name as "name" , abs_vend_acct_type_table.name as "name" , Debit."cumm_dbt_blnce" - Credit."cumm_crd_blnce" as "undefined" , Debit."cumm_dbt_blnce" as "cumm_dbt_blnce" , Credit."cumm_crd_blnce" as "cumm_crd_blnce" , cred_vend_acct_table.objid as "__OBJID__" , cred_vend_acct_table.clsref as "__CLSREF__" from only cred_vend_acct_table , abs_vend_acct_type_table , ( select daily_acct_blnce_table.cumm_dbt_blnce as "cumm_dbt_blnce" from only daily_acct_blnce_table where ( ( ( daily_acct_blnce_table.company_objid *= 2147483647 ) ) and ( ( daily_acct_blnce_table.status = 'e' ) ) and ( daily_acct_blnce_table.abs_acct_objref[2] = cred_vend_acct_table.objid ) and ( daily_acct_blnce_table.abs_acct_objref[1] = cred_vend_acct_table.clsref ) and daily_acct_blnce_table.dte = ( select max(daily_acct_blnce_table.dte) as "dte" from only daily_acct_blnce_table where ( ( ( daily_acct_blnce_table.company_objid *= 147483647 ) ) and ( ( daily_acct_blnce_table.status = 'e' ) ) and ( daily_acct_blnce_table.abs_acct_objref[2] = cred_vend_acct_table.objid ) and ( daily_acct_blnce_table.abs_acct_objref[1] = cred_vend_acct_table.clsref ) ) ) ) ) as Debit , ( select daily_acct_blnce_table.cumm_crd_blnce as "cumm_crd_blnce" from only daily_acct_blnce_table where ( ( ( daily_acct_blnce_table.company_objid *= 2147483647 ) ) and ( ( daily_acct_blnce_table.status = 'e' ) ) and ( daily_acct_blnce_table.abs_acct_objref[2] = cred_vend_acct_table.objid ) and ( daily_acct_blnce_table.abs_acct_objref[1] = cred_vend_acct_table.clsref ) and daily_acct_blnce_table.dte = ( select max(daily_acct_blnce_table.dte) as "dte" from only daily_acct_blnce_table where ( ( ( daily_acct_blnce_table.company_objid *= 2147483647 ) ) and ( ( daily_acct_blnce_table.status = 'e' ) ) and ( daily_acct_blnce_table.abs_acct_objref[2] = cred_vend_acct_table.objid ) and ( daily_acct_blnce_table.abs_acct_objref[1] = cred_vend_acct_table.clsref ) ) ) ) ) as Credit where ( ( ( cred_vend_acct_table.company_objid *= 2147483647 ) and ( abs_vend_acct_type_table.company_objid *= 2147483647 ) ) and ( ( cred_vend_acct_table.status = 'e' ) and ( abs_vend_acct_type_table.status = 'e' ) ) and cred_vend_acct_table.owner_objref[1] = 100110 and cred_vend_acct_table.owner_objref[2] = 2147483647 and ( ( ( cred_vend_acct_table.abs_acct_type_objref[1] = abs_vend_acct_type_table.clsref ) and ( cred_vend_acct_table.abs_acct_type_objref[2] = abs_vend_acct_type_table.objid ) ) ) ) order by 2 asc limit 100 Regards, Floyd Shackelford 4 Peaks Technology Group, Inc. VOICE: 334.735.9428 FAX: 702.995.6462 EMAIL: [EMAIL PROTECTED] ICQ #: 161371538 PGP Fone at private.fwshackelford.com on request Shackelford Motto: ACTA NON VERBA - Actions, not words Alabama StateMotto: AUDEMUS JURA NOSTRA DEFENDERE - We Dare Defend Our Rights The Philosophy of Liberty: http://www.isil.org/resources/introduction.swf ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] summing tables
The primary problem was that the update command doesn't modify rows in the order u want to do it. I think the update starts with the latest inserted rows. I guess. Anyway, in real life this update modifies only one row with a value wich is diff of null. It was really handy if it was specified the option ORDER for the update command. - Original Message - From: "Greg Stark" <[EMAIL PROTECTED]> To: "Viorel Dragomir" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Tuesday, July 15, 2003 5:55 PM Subject: Re: [SQL] summing tables > > To solve this problem efficiently you probably need the lead/lag analytic > functions. Unfortunately Postgres doesn't have them. > > You could do it with something like: > > update foo set c = a+b+(select c from foo as x where seq < foo.seq ORDER BY seq desc LIMIT 1) > > or the more standard but likely to be way slower: > > update foo set c = a+b+(select c from foo as x where seq = (select max(seq) from foo as y where seq < foo.seq)) > > > However, i would suggest that if you have an implicit relationship between > records you should make that relationship explicit with a foreign key. If you > had a column that contained the seq of the parent record then this would be > easy. I'm really puzzled how this query as currently specified could be > useful. > > > -- > greg > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] min() and NaN
Hi, I have a table containing a double precision column. That column contains at least one judiciously placed NaN. I'd expect the aggregate function min() to return the minimum, valid numeric value. Instead, it seems to return the minimum value from the subset of rows following the 'NaN'. What's going here? What should I expect the aggregate function min() to return in this case? And why? Any help is appreciated, Mike testdb=> \d min_with_nan Table "public.min_with_nan" Column | Type | Modifiers +--+--- col1 | double precision | testdb=> select * from min_with_nan ; col1 --- 3.141 2.718 NaN 10 (4 rows) testdb=> select min(col1) from min_with_nan ; min - 10 (1 row) testdb=> select min(col1) from min_with_nan where col1 != 'NaN'; min --- 2.718 (1 row) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Datatype Conversion help
How do i can convert character varying type to integer ___ Click below to experience Sooraj R Barjatya's latest offering 'Main Prem Ki Diwani Hoon' starring Hrithik, Abhishek & Kareena http://www.mpkdh.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] Timestamp problem
I am use PostgreSQL 7.2.1 in Redhat Linux 7.2 Java 1.4.x When I do JDBC String sql = "SELECT datetime_column FROM mytable"; Timestamp ts = resultSet.getTimestamp(2); If the timestamp return format like 2003-07-15 13:20:00.20+00 then is OK. But if timestamp return like 2003-07-15 13:20:00.421+00 Then I got SQLException Bad Timestamp Format at 23 in 2003-07-14 14:45:00.421+00 What is 23? How do I resolve this problem? Thank Q! -- Raymond Chui NWS at NOAA 301-713-0640 ext 168 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] changing an update via rules
Hello, I am having difficulty setting an on update rule which seems to be caught in a recursive loop. Context is a table with three columns assigning users to groups with the third column being boolean to flag the primary group. I would like to set an update rule to enforce one primary group. Any suggestions on how to do this, or exactly how the code is getting trapped? My code is below: Thank you, Lauren Matheson. --change all others primarygroup to false when ours is true CREATE RULE contactsecgrp_update_1pg_them AS ON UPDATE TO contactsecuritygroup WHERE NEW.primarygroup=true DO UPDATE contactsecuritygroup SET primarygroup=false WHERE contact_id = NEW.contact_id and securitygroup_id != NEW.securitygroup_id; --change our primarygroup to true when no true exists and we were false CREATE RULE contactsecgrp_update_1pg_us AS ON UPDATE TO contactsecuritygroup WHERE NEW.primarygroup=false and (select count(contact_id) from contactsecuritygroup where contact_id=NEW.contact_id AND primarygroup=true)=0 DO UPDATE contactsecuritygroup SET primarygroup=true WHERE contact_id = NEW.contact_id and securitygroup_id = NEW.securitygroup_id; ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Recursive request ...
Thx a lot, I know now that it is possible to do that with pure sql. Have you the alogorythm, because your link is dead ? Ben Rajesh Kumar Mallah a écrit: Dear Bournon, There are already good implementation of Tree structures in databases ranging from using pure SQL to PostgreSQL specfic methods , less point in revinting wheel unless u really need. Some Pointers: "Tree-structure functions" http://www.brasileiro.net:8080/postgres/cookbook/ Gist Based: contrib/ltree Joe Celko's Article on "Nested Sets & Adjacency Lists" http://www.ibase.ru/devinfo/DBMSTrees/sqltrees.html http://users.starpower.net/rjhalljr/Serve/MySQL/traer.html http://www.google.com/search?hl=en&ie=UTF-8&oe=UTF-8&q=adjacency+list+%2B+tree+%2B+joe On Wednesday 09 Jul 2003 8:57 pm, Benoît Bournon wrote: I have to make a function that returns a tree with title and link of a table. Recursively, a information depends on a parent information. It is to organise a menu with parent dependance. How is it possible and faster ? in C ? pl/pgsql or other ? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Cannot insert dup id in pk
Hello,
I am having strange behavior when I try to do an insert. Postgres tells
me that it cannot insert a duplicate key into a primary key index, when
I am not trying to insert into it. Can anyone shed light on why this is
happen, or point out the error of my ways?
Here are the details:
wormbase=> \d fdata
Table "public.fdata"
Column | Type | Modifiers
---++---
-
fid | integer| not null default nextval('public.fdata
_fid_seq'::text)
fref | character varying(100) | not null default ''
fstart| integer| not null default '0'
fstop | integer| not null default '0'
fbin | double precision | not null default '0.00'
ftypeid | integer| not null default '0'
fscore| double precision |
fstrand | character varying(3) |
fphase| character varying(3) |
gid | integer| not null default '0'
ftarget_start | integer|
ftarget_stop | integer|
Indexes: pk_fdata primary key btree (fid),
fdata_fref_idx btree (fref, fbin, fstart, fstop, ftypeid, gid),
fdata_ftypeid_idx btree (ftypeid),
fdata_gid_idx btree (gid)
Check constraints: "chk_fdata_fstrand" ((fstrand = '+'::character varying) OR (f
strand = '-'::character varying))
"chk_fdata_fphase" (((fphase = '0'::character varying) OR (fp hase
= '1'::character varying)) OR (fphase = '2'::character varying))
Now a chunk from my query log:
Jul 14 12:48:47 localhost postgres[2998]: [107-1] LOG: query: INSERT INTO fdata
(fref,fstart,fstop,fbin,ftypeid,fscore,fstrand,fphase,gid,ftarget_start,ftarget_stop)
Jul 14 12:48:47 localhost postgres[2998]: [107-2]
VALUES('XX',7327656,7327658,'1000.007327','41',NULL,'+',NULL,'12358',NULL,NULL)
Jul 14 12:48:47 localhost postgres[2998]: [108] ERROR: Cannot insert a duplicate key
into unique index pk_fdata
Note that I do not try to insert anything into fid, the primary key on
this table. Why does Postgres think I am?
Thanks much,
Scott
--
Scott Cain, Ph. D. [EMAIL PROTECTED]
GMOD Coordinator (http://www.gmod.org/) 216-392-3087
Cold Spring Harbor Laboratory
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] How to write this query!
These are my PostgreSQL tables: pid | name 1 | A 2 | B 3 | C 4 | D 5 | E tid | pid 1 | pid 2 | pid 3 1 | 1| 2| 3 Bascially, I would like to write a query to list only the names which their "pid" match those pids in the other table. If anyone knows, pls help!! Many Thanks!! Joe Want to chat instantly with your online friends? Get the FREE Yahoo! Messenger http://uk.messenger.yahoo.com/ ---(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] Problem with temporary table -- Urgent
On Thu, 10 Jul 2003, Vijay Kumar wrote:
> Hi,
> We are using postgresql 7.3.3, we are encountering the following problems when we
> used temporary tables.
>
> Here with i'm sending my Sample function.
>
> create or replace function TestTemp_refcur(refcursor) returns refcursor As '
> declare
> refc alias for $1;
> lString Varchar(4000);
> begin
> lString := '' CREATE OR REPLACE FUNCTION TESTTEMP() RETURNS varchar as
>
> BEGIN '';
> lString := lString || '' create temporary table temp_Table( Name
> Numeric);'';
>
> lString := lString || '' insert into temp_Table values (1); '';
>
> lString := lString || '' insert into temp_Table values (2);'';
>
> lString := lString || '' return null; end; language plpgsql;'';
>
> raise notice '' Notice is % '', lString;
> execute lString;
Okay, this create a function which would create the table and insert some
stuff. But you don't actually appear to call it afaics.
> open refc for select * from temp_Table;
I think you may need to use open for execute here as well to make it work
with temporary tables in any case.
> return refc;
> end;
> ' language 'plpgsql';
>
> begin;
> select TestTemp_refcur('funcursor');
> fetch all in funcursor;
> commit;
>
>
> psql:test18.sql:25: WARNING: Error occurred while executing PL/pgSQL function t
> esttemp_refcur
> psql:test18.sql:25: WARNING: line 20 at open
> psql:test18.sql:25: ERROR: Relation "temp_table" does not exist
> psql:test18.sql:26: ERROR: current transaction is aborted, queries ignored unti
> l end of transaction block
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] unique value - trigger?
On Thu, Jul 17, 2003 at 12:56:52PM +0100, Gary Stainburn wrote: > > nymr=# \d lnumbers >Table "lnumbers" > Column | Type | Modifiers > ---+---+--- > lnid | integer | not null > lnumber | character varying(10) | not null > lncurrent | boolean | > Primary key: lnumbers_pkey > Triggers: RI_ConstraintTrigger_7575462 > > I want to make it so that if I set lncurrent to true for one row, any existing > true rows are set to false. > > I'm guessing that I need to create a trigger to be actioned after an insert or > update which would update set lncurrent=false where lnid not = Absolutely. Something like this will work: CREATE FUNCTION lnumbers_current_trigger() RETURNS TRIGGER AS ' BEGIN IF NEW.lncurrent THEN UPDATE lnumbers SET lncurrent = ''f'' WHERE lnid = NEW.lnid AND lnumber != NEW.lnumber AND lncurrent = ''t''; END IF; RETURN NEW; END' LANGUAGE 'plpgsql'; CREATE TRIGGER lnumbers_current AFTER UPDATE OR INSERT ON lnumbers FOR EACH ROW EXECUTE PROCEDURE lnumbers_current_trigger(); (Lightly tested only on 7.3.3) In the WHERE condition in the function, specifying "lncurrent = 't'" means that we don't update more rows than we have to. Making the trigger fire AFTER INSERT as well as AFTER UPDATE means that if you just add a new row with lncurrent set to true, it Does The Right Thing. In this particular example, the trigger will work perfectly well as a BEFORE, also. If you can't or don't want to install PL/PgSQL (or some other procedural language), you can do it with rules. It's more long-winded that way, although I also think it's more elegant... Richard ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] "Truncate [ Table ] name [Cascade]"?
Hi there, Would it be possible to implement some kind of cascading truncate? As far as I understand, the "no truncate if table is referenced" change was introduced to ensure database integrity. However, if the referencing table is truncated, too, there should be no problems as far as foreign keys are concerned, correct? Another option that seems feasible to me: How about allowing truncates on tables that are only referenced (if at all) by ones with no entries? Since no data is actually "enforcing" the foreign key restriction, truncating the table should be safe ... The rationale behind this suggestion is that we need a quick way to purge the entries in all tables in order to accelerate the reinitialization of the tables for our unit tests. If you know of some better way to truncate all tables, please let us know it. Thanks for your time, Andi. ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] How access to array component
Hello How access to especific array component of an function with return type _varchar declaration of my function: >>> create or replace __function( int2, int2 ) returns _varchar now use the function in SQL and access to the element 1... >>> select __function(10::int2, 20::int2)[1] ^^^ parsing error. Thanks. Cristian Cappo - ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Return a set of values from postgres Function
I want to get a set of values returned from a function. The values (there will need to be four of them) come from 4 separate SELECT statements inside the Function. For example SELECT one INTO variable1 from table1 where ... then SELECT two INTO variable2 from table1 where... At first you might think I could just combine the select statements into one statement, but I can't. I need to pass the results of each separate select statement into distinct variables and return them together to the application. This is what I'm using but isn't working create function allincomes(int4) RETURNS setof RECORD AS ' declare vResult RECORD; begin select into vResult CAST(monthly AS varchar) as w2client from income where caseid = $1 and incometype = ''W2'' and who = ''Client''; select into vResult CAST(monthly AS varchar) as selfemployedclient from income where caseid = $1 and incometype = ''Self Employed'' and who = ''Client''; return ; end; ' language 'plpgsql'; Any ideas? Thanks, Derrick
Re: [SQL] column doesn't get calculated - update # 2
i was wrong. it doesn't work as a prepared statement nor as a dynamic string using jdbc. it works fine if i paste it into psql. is it possible that a problem with a calculated column and a subselect in conjunction is a jdbc bug? Regards, Floyd Shackelford 4 Peaks Technology Group, Inc. VOICE: 334.735.9428 FAX: 702.995.6462 EMAIL: [EMAIL PROTECTED] ICQ #: 161371538 PGP Fone at private.fwshackelford.com on request Shackelford Motto: ACTA NON VERBA - Actions, not words Alabama StateMotto: AUDEMUS JURA NOSTRA DEFENDERE - We Dare Defend Our Rights The Philosophy of Liberty: http://www.isil.org/resources/introduction.swf -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 16, 2003 10:05 AM To: Pgsql-Sql Cc: Josh Wardle; Gregory S. Dodson Subject: RE: column doesn't get calculated - updated when i copy/paste the select stmt into psql, it works. or if i use it "dynamically". it doesn't work properly when i use it in a prepared statement -- which is what i am doing. Regards, Floyd Shackelford 4 Peaks Technology Group, Inc. VOICE: 334.735.9428 FAX: 702.995.6462 EMAIL: [EMAIL PROTECTED] ICQ #: 161371538 PGP Fone at private.fwshackelford.com on request Shackelford Motto: ACTA NON VERBA - Actions, not words Alabama StateMotto: AUDEMUS JURA NOSTRA DEFENDERE - We Dare Defend Our Rights The Philosophy of Liberty: http://www.isil.org/resources/introduction.swf -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 15, 2003 11:14 AM To: Pgsql-Sql Subject: column doesn't get calculated this must be a problem with my sql, but this one has me stumped. the column: Debit."cumm_dbt_blnce" - Credit."cumm_crd_blnce" in the long sql statement below comes out as the literal: Debit."cumm_dbt_blnce" - Credit."cumm_crd_blnce" rather than as the calculated value. WHY!?!?! this works with simple sql in psql: select a."field1" - b."field2" from (select field1 as "field1" from someTable) as a, (select field2 as "field2" from someOtherTable) as b; but with my more complex sql, it doesn't. it comes out as a literal string. it's almost like postgresql forgot to process this column. select cred_vend_acct_table.num as "num" , cred_vend_acct_table.name as "name" , abs_vend_acct_type_table.name as "name" , Debit."cumm_dbt_blnce" - Credit."cumm_crd_blnce" as "undefined" , Debit."cumm_dbt_blnce" as "cumm_dbt_blnce" , Credit."cumm_crd_blnce" as "cumm_crd_blnce" , cred_vend_acct_table.objid as "__OBJID__" , cred_vend_acct_table.clsref as "__CLSREF__" from only cred_vend_acct_table , abs_vend_acct_type_table , ( select daily_acct_blnce_table.cumm_dbt_blnce as "cumm_dbt_blnce" from only daily_acct_blnce_table where ( ( ( daily_acct_blnce_table.company_objid *= 2147483647 ) ) and ( ( daily_acct_blnce_table.status = 'e' ) ) and ( daily_acct_blnce_table.abs_acct_objref[2] = cred_vend_acct_table.objid ) and ( daily_acct_blnce_table.abs_acct_objref[1] = cred_vend_acct_table.clsref ) and daily_acct_blnce_table.dte = ( select max(daily_acct_blnce_table.dte) as "dte" from only daily_acct_blnce_table where ( ( ( daily_acct_blnce_table.company_objid *= 147483647 ) ) and ( ( daily_acct_blnce_table.status = 'e' ) ) and ( daily_acct_blnce_table.abs_acct_objref[2] = cred_vend_acct_table.objid ) and ( daily_acct_blnce_table.abs_acct_objref[1] = cred_vend_acct_table.clsref ) ) ) ) ) as Debit , ( select daily_acct_blnce_table.cumm_crd_blnce as "cumm_crd_blnce" from only daily_acct_blnce_table where ( ( ( daily_acct_blnce_table.company_objid *= 2147483647 ) ) and ( ( daily_acct_blnce_table.status = 'e' ) ) and ( daily_acct_blnce_table.abs_acct_objref[2] = cred_vend_acct_table.objid ) and ( daily_acct_blnce_table.abs_acct_objref[1] = cred_vend_acct_table.clsref ) and daily_acct_blnce_table.dte = ( select max(daily_acct_blnce_table.dte) as "dte" from only daily_acct_blnce_table where ( ( ( daily_acct_blnce_table.company_objid *= 2147483647 ) ) and ( ( daily_acct_blnce_table.status = 'e' ) ) and ( daily_acct_blnce_table.abs_acct_objref[2] = cred_vend_acct_table.objid ) and ( daily_acct_blnce_table.abs_acct_objref[1] = cred_vend_acct_table.clsref ) ) ) ) ) as Credit where ( ( ( cred_vend_acct_table.company_objid *= 2147483647 ) and ( abs_vend_acct_type_table.company_objid *= 2147483647 ) ) and ( ( cred_vend_acct_table.status = 'e' ) and ( abs_vend_acct_type_table.status =
Re: [SQL] column doesn't get calculated - updated
when i copy/paste the select stmt into psql, it works. or if i use it "dynamically". it doesn't work properly when i use it in a prepared statement -- which is what i am doing. Regards, Floyd Shackelford 4 Peaks Technology Group, Inc. VOICE: 334.735.9428 FAX: 702.995.6462 EMAIL: [EMAIL PROTECTED] ICQ #: 161371538 PGP Fone at private.fwshackelford.com on request Shackelford Motto: ACTA NON VERBA - Actions, not words Alabama StateMotto: AUDEMUS JURA NOSTRA DEFENDERE - We Dare Defend Our Rights The Philosophy of Liberty: http://www.isil.org/resources/introduction.swf -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 15, 2003 11:14 AM To: Pgsql-Sql Subject: column doesn't get calculated this must be a problem with my sql, but this one has me stumped. the column: Debit."cumm_dbt_blnce" - Credit."cumm_crd_blnce" in the long sql statement below comes out as the literal: Debit."cumm_dbt_blnce" - Credit."cumm_crd_blnce" rather than as the calculated value. WHY!?!?! this works with simple sql: select a."field1" - b."field2" from (select field1 as "field1" from someTable) as a, (select field2 as "field2" from someOtherTable) as b; but with my more complex sql, it doesn't. it comes out as a literal string. it's almost like postgresql forgot to process this column. select cred_vend_acct_table.num as "num" , cred_vend_acct_table.name as "name" , abs_vend_acct_type_table.name as "name" , Debit."cumm_dbt_blnce" - Credit."cumm_crd_blnce" as "undefined" , Debit."cumm_dbt_blnce" as "cumm_dbt_blnce" , Credit."cumm_crd_blnce" as "cumm_crd_blnce" , cred_vend_acct_table.objid as "__OBJID__" , cred_vend_acct_table.clsref as "__CLSREF__" from only cred_vend_acct_table , abs_vend_acct_type_table , ( select daily_acct_blnce_table.cumm_dbt_blnce as "cumm_dbt_blnce" from only daily_acct_blnce_table where ( ( ( daily_acct_blnce_table.company_objid *= 2147483647 ) ) and ( ( daily_acct_blnce_table.status = 'e' ) ) and ( daily_acct_blnce_table.abs_acct_objref[2] = cred_vend_acct_table.objid ) and ( daily_acct_blnce_table.abs_acct_objref[1] = cred_vend_acct_table.clsref ) and daily_acct_blnce_table.dte = ( select max(daily_acct_blnce_table.dte) as "dte" from only daily_acct_blnce_table where ( ( ( daily_acct_blnce_table.company_objid *= 147483647 ) ) and ( ( daily_acct_blnce_table.status = 'e' ) ) and ( daily_acct_blnce_table.abs_acct_objref[2] = cred_vend_acct_table.objid ) and ( daily_acct_blnce_table.abs_acct_objref[1] = cred_vend_acct_table.clsref ) ) ) ) ) as Debit , ( select daily_acct_blnce_table.cumm_crd_blnce as "cumm_crd_blnce" from only daily_acct_blnce_table where ( ( ( daily_acct_blnce_table.company_objid *= 2147483647 ) ) and ( ( daily_acct_blnce_table.status = 'e' ) ) and ( daily_acct_blnce_table.abs_acct_objref[2] = cred_vend_acct_table.objid ) and ( daily_acct_blnce_table.abs_acct_objref[1] = cred_vend_acct_table.clsref ) and daily_acct_blnce_table.dte = ( select max(daily_acct_blnce_table.dte) as "dte" from only daily_acct_blnce_table where ( ( ( daily_acct_blnce_table.company_objid *= 2147483647 ) ) and ( ( daily_acct_blnce_table.status = 'e' ) ) and ( daily_acct_blnce_table.abs_acct_objref[2] = cred_vend_acct_table.objid ) and ( daily_acct_blnce_table.abs_acct_objref[1] = cred_vend_acct_table.clsref ) ) ) ) ) as Credit where ( ( ( cred_vend_acct_table.company_objid *= 2147483647 ) and ( abs_vend_acct_type_table.company_objid *= 2147483647 ) ) and ( ( cred_vend_acct_table.status = 'e' ) and ( abs_vend_acct_type_table.status = 'e' ) ) and cred_vend_acct_table.owner_objref[1] = 100110 and cred_vend_acct_table.owner_objref[2] = 2147483647 and ( ( ( cred_vend_acct_table.abs_acct_type_objref[1] = abs_vend_acct_type_table.clsref ) and ( cred_vend_acct_table.abs_acct_type_objref[2] = abs_vend_acct_type_table.objid ) ) ) ) order by 2 asc limit 100 Regards, Floyd Shackelford 4 Peaks Technology Group, Inc. VOICE: 334.735.9428 FAX: 702.995.6462 EMAIL: [EMAIL PROTECTED] ICQ #: 161371538 PGP Fone at private.fwshackelford.com on request Shackelford Motto: ACTA NON VERBA - Actions, not words Alabama StateMotto: AUDEMUS JURA NOSTRA DEFENDERE - We Dare Defend Our Rights The Philosophy of Liberty: http://www.isil.org/resources/introduction.swf ---(end of broadcast)--- TIP 7: don't forget to increase
[SQL] Why do the two queries below return different results?
Look at the following two queries.
select casepid, origpid
from virbcase
where date_trunc('day', origdt) >= '2003-07-01'
and date_trunc('day', origdt) <= '2003-07-31'
and origpid in (select pid from party where partyid in ('00339',
'00310',
'00320'))
and not exists (select pid from casecombo where casepid = secondpid)
select casepid, origpid
from virbcase
where date_trunc('day', origdt) >= '2003-07-01'
and date_trunc('day', origdt) <= '2003-07-31'
and origpid in (select pid from party where partyid in ('00339',
'00310',
'00320'))
and casepid not in (select secondpid from casecombo)
The second query is broken and I don't understand why.
Correlating the subquery as is done in the first query fixes it.
--
Warren Little
Senior Vice President
Secondary Marketing
Security Lending Wholesale, LC
www.securitylending.com
Tel: 866-369-7763
Fax: 866-849-8082
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Recursive request ...
I see connect by in Oracle ??? it is equivalent in PostgreSQL or not ?? Rajesh Kumar Mallah a écrit: Dear Bournon, There are already good implementation of Tree structures in databases ranging from using pure SQL to PostgreSQL specfic methods , less point in revinting wheel unless u really need. Some Pointers: "Tree-structure functions" http://www.brasileiro.net:8080/postgres/cookbook/ Gist Based: contrib/ltree Joe Celko's Article on "Nested Sets & Adjacency Lists" http://www.ibase.ru/devinfo/DBMSTrees/sqltrees.html http://users.starpower.net/rjhalljr/Serve/MySQL/traer.html http://www.google.com/search?hl=en&ie=UTF-8&oe=UTF-8&q=adjacency+list+%2B+tree+%2B+joe On Wednesday 09 Jul 2003 8:57 pm, Benoît Bournon wrote: I have to make a function that returns a tree with title and link of a table. Recursively, a information depends on a parent information. It is to organise a menu with parent dependance. How is it possible and faster ? in C ? pl/pgsql or other ? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Join table with itself for heirarchial system?
Can you query a set of nested entries to simulate a heirarchial system with a single query? I'm building a nested category table with a definition like below" CREATE TABLE category ( id serial, parent integer not null, title varchar); Idea is that we can "nest" categories so that we have id parent title -- 1 0 Clothing 2 1 Shirts 3 1 Pants 4 1 Socks 5 4 Male 6 4 Silk So that, for example, id 6 would be Clothing -> Socks -> Silk. So far, I've only been able to derive this with 3 queries - 1 to get the parent for id #6 (Silk) another to get the parent for id #4 (Socks) and finally for id #1 (Clothing) and since parent ==0 I stop. This seems wasteful - can this be done in a single query? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] [JDBC] column doesn't get calculated - update # 2
Floyd, I would recommend turning sql statement tracing on in the database to see the exact sql text that the driver is sending to the database (in case it is somehow munging it). Then take that exact same text (as found in the server log files) and run it in psql to see how it works there. --Barry [EMAIL PROTECTED] wrote: i was wrong. it doesn't work as a prepared statement nor as a dynamic string using jdbc. it works fine if i paste it into psql. is it possible that a problem with a calculated column and a subselect in conjunction is a jdbc bug? Regards, Floyd Shackelford 4 Peaks Technology Group, Inc. VOICE: 334.735.9428 FAX: 702.995.6462 EMAIL: [EMAIL PROTECTED] ICQ #: 161371538 PGP Fone at private.fwshackelford.com on request Shackelford Motto: ACTA NON VERBA - Actions, not words Alabama StateMotto: AUDEMUS JURA NOSTRA DEFENDERE - We Dare Defend Our Rights The Philosophy of Liberty: http://www.isil.org/resources/introduction.swf -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 16, 2003 10:05 AM To: Pgsql-Sql Cc: Josh Wardle; Gregory S. Dodson Subject: RE: column doesn't get calculated - updated when i copy/paste the select stmt into psql, it works. or if i use it "dynamically". it doesn't work properly when i use it in a prepared statement -- which is what i am doing. Regards, Floyd Shackelford 4 Peaks Technology Group, Inc. VOICE: 334.735.9428 FAX: 702.995.6462 EMAIL: [EMAIL PROTECTED] ICQ #: 161371538 PGP Fone at private.fwshackelford.com on request Shackelford Motto: ACTA NON VERBA - Actions, not words Alabama StateMotto: AUDEMUS JURA NOSTRA DEFENDERE - We Dare Defend Our Rights The Philosophy of Liberty: http://www.isil.org/resources/introduction.swf -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 15, 2003 11:14 AM To: Pgsql-Sql Subject: column doesn't get calculated this must be a problem with my sql, but this one has me stumped. the column: Debit."cumm_dbt_blnce" - Credit."cumm_crd_blnce" in the long sql statement below comes out as the literal: Debit."cumm_dbt_blnce" - Credit."cumm_crd_blnce" rather than as the calculated value. WHY!?!?! this works with simple sql in psql: select a."field1" - b."field2" from (select field1 as "field1" from someTable) as a, (select field2 as "field2" from someOtherTable) as b; but with my more complex sql, it doesn't. it comes out as a literal string. it's almost like postgresql forgot to process this column. select cred_vend_acct_table.num as "num" , cred_vend_acct_table.name as "name" , abs_vend_acct_type_table.name as "name" , Debit."cumm_dbt_blnce" - Credit."cumm_crd_blnce" as "undefined" , Debit."cumm_dbt_blnce" as "cumm_dbt_blnce" , Credit."cumm_crd_blnce" as "cumm_crd_blnce" , cred_vend_acct_table.objid as "__OBJID__" , cred_vend_acct_table.clsref as "__CLSREF__" from only cred_vend_acct_table , abs_vend_acct_type_table , ( select daily_acct_blnce_table.cumm_dbt_blnce as "cumm_dbt_blnce" from only daily_acct_blnce_table where ( ( ( daily_acct_blnce_table.company_objid *= 2147483647 ) ) and ( ( daily_acct_blnce_table.status = 'e' ) ) and ( daily_acct_blnce_table.abs_acct_objref[2] = cred_vend_acct_table.objid ) and ( daily_acct_blnce_table.abs_acct_objref[1] = cred_vend_acct_table.clsref ) and daily_acct_blnce_table.dte = ( select max(daily_acct_blnce_table.dte) as "dte" from only daily_acct_blnce_table where ( ( ( daily_acct_blnce_table.company_objid *= 147483647 ) ) and ( ( daily_acct_blnce_table.status = 'e' ) ) and ( daily_acct_blnce_table.abs_acct_objref[2] = cred_vend_acct_table.objid ) and ( daily_acct_blnce_table.abs_acct_objref[1] = cred_vend_acct_table.clsref ) ) ) ) ) as Debit , ( select daily_acct_blnce_table.cumm_crd_blnce as "cumm_crd_blnce" from only daily_acct_blnce_table where ( ( ( daily_acct_blnce_table.company_objid *= 2147483647 ) ) and ( ( daily_acct_blnce_table.status = 'e' ) ) and ( daily_acct_blnce_table.abs_acct_objref[2] = cred_vend_acct_table.objid ) and ( daily_acct_blnce_table.abs_acct_objref[1] = cred_vend_acct_table.clsref ) and daily_acct_blnce_table.dte = ( select max(daily_acct_blnce_table.dte) as "dte" from only daily_acct_blnce_table where ( ( ( daily_acct_blnce_table.company_objid *= 2147483647 ) ) and ( ( daily_acct_blnce_table.status = 'e' ) ) and ( daily_acct_blnce_table.abs_acct_objref[2] = cred_vend_acct_table.objid ) and ( daily_acct_bl
Re: [SQL] (trigger function) -> ERROR: NEW used in non-rule query
Josh Berkus wrote: Terence, Oh well, can't win them all :/ Nope. I'll suggest that for the TODO list ... we already have several requests for added features for PL/pgSQL. The problem is that we currently don't have a lead developer for PL/pgSQL, so the language has rather stagnated. Well beggers can't be choosers. I find it entirely amazing that one has access to such a capable product entirely free of charge :) While people like myself may raise issues from time to time and sometimes express frustration, it doesn't mean that we don't think that postgres (and associated tools) is absolutely awsome. I'm more than content to "make do" ;^) with what postgres has now. Well I suppose I could try TCL. The problem is that there is little to no documentation on postgres stored procedures in TCL and I've never even seen the language before. None the less, I'll look into it. It's almost worth it. If that fails, I may even try perl . And what's wrong with Perl? Other than the inability to write triggers with it? (We want to enable triggers in PL/perl, but that functionality isn't coming until at least 7.5). Let's just say I don't belong to the very large community who LOVE the language. Don't get too excited, it's just my personal preference. I don't find the language "easy" at all (which is how it is towted). One of these days when I get "spare time" (and the moon is blue), then I will hit it again and maybe do it justice in terms of effort. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] How access to array component
Cristian Cappo wrote:
>>> select __function(10::int2, 20::int2)[1]
^^^ parsing error.
Try:
create or replace function foo(int2, int2 ) returns _varchar as '
select ''{1,2}''::_varchar
' language 'sql';
regression=# select (foo(10::int2, 20::int2))[1];
foo
-
1
(1 row)
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])
[SQL] SECURITY DEFINER changes CURRENT_USER?
Hi all; I found an unexpected behavior while trying to write a function to allow users to change their own passwords. The function is as follows: CREATE OR REPLACE FUNCTION change_password(VARCHAR) RETURNS BOOL AS ' DECLARE username VARCHAR; CMD VARCHAR; password ALIAS FOR $1; BEGIN SELECT INTO username CURRENT_USER; CMD := ''ALTER USER '' || username || '' WITH PASSWORD ''; CMD := CMD || || password || ; EXECUTE CMD; RETURN TRUE; end; ' LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER I would expect this to change the password of the user currently logged in but instead it changes MY password. Evidently when a function is called which is set to SECURITY DEFINER, it changes the context of the current user. The CURRENT_USER then returns the name of the definer rather than the invoker of the function. So this being said-- are there any workarounds that don't allow anyone to change anyone else's password? Best Wishes, Chris Travers ---(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] Why do the two queries below return different results?
Warren Little <[EMAIL PROTECTED]> writes: > Look at the following two queries. > ... > and not exists (select pid from casecombo where casepid = secondpid) > ... > and casepid not in (select secondpid from casecombo) > The second query is broken and I don't understand why. I'll bet there are some NULL values for secondpid in casecombo. The behavior of NOT IN with NULLs is fairly unintuitive :-( regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] column doesn't get calculated - updated
<[EMAIL PROTECTED]> writes: > when i copy/paste the select stmt into psql, it works. or if i use it > "dynamically". it doesn't work properly when i use it in a prepared > statement -- which is what i am doing. Could we see a self-contained example of the problem? It's hard to tell whether you are dealing with a bug or pilot error ... regards, tom lane ---(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] min() and NaN
"Michael S. Tibbetts" <[EMAIL PROTECTED]> writes: > I'd expect the aggregate function min() to return the minimum, valid > numeric value. Instead, it seems to return the minimum value from the > subset of rows following the 'NaN'. Not real surprising given than min() is implemented with float8smaller, which does this: result = ((arg1 > arg2) ? arg1 : arg2); In most C implementations, any comparison involving a NaN will return "false". So when we hit the NaN, we have arg1 = min so far, arg2 = NaN, comparison yields false, result is NaN. On the next row, we have arg1 = NaN, arg2 = next value, comparison yields false, result is next value; and away it goes. We could probably make it work the way you want with explicit tests for NaN in float8smaller, arranged to make sure that the result is not NaN unless both inputs are NaN. But I'm not entirely convinced that we should make it work like that. The other float8 comparison operators are designed to treat NaN as larger than every other float8 value (so that it has a well-defined position when sorting), and I'm inclined to think that float8smaller and float8larger probably should behave likewise. (That actually is the same as what you want for MIN(), but not for MAX() ...) Comments anyone? regards, tom lane ---(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] min() and NaN
On Sun, 20 Jul 2003, Tom Lane wrote: > "Michael S. Tibbetts" <[EMAIL PROTECTED]> writes: > > I'd expect the aggregate function min() to return the minimum, valid > > numeric value. Instead, it seems to return the minimum value from the > > subset of rows following the 'NaN'. > > Not real surprising given than min() is implemented with float8smaller, > which does this: > > result = ((arg1 > arg2) ? arg1 : arg2); > > In most C implementations, any comparison involving a NaN will return > "false". So when we hit the NaN, we have arg1 = min so far, arg2 = NaN, > comparison yields false, result is NaN. On the next row, we have > arg1 = NaN, arg2 = next value, comparison yields false, result is next > value; and away it goes. > > We could probably make it work the way you want with explicit tests for > NaN in float8smaller, arranged to make sure that the result is not NaN > unless both inputs are NaN. But I'm not entirely convinced that we > should make it work like that. The other float8 comparison operators > are designed to treat NaN as larger than every other float8 value (so > that it has a well-defined position when sorting), and I'm inclined to > think that float8smaller and float8larger probably should behave > likewise. (That actually is the same as what you want for MIN(), but > not for MAX() ...) The spec seems to say that min/max should work the same way as the comparison operators by saying that it returns the maximum or minimum value as determined by the comparison rules of the comparison predicate section. That'd seem to be asking for the second version. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Return a set of values from postgres Function
On Thu, 17 Jul 2003, Derrick Betts wrote: > I want to get a set of values returned from a function. The values > (there will need to be four of them) come from 4 separate SELECT > statements inside the Function. For example SELECT one INTO variable1 > from table1 where ... then SELECT two INTO variable2 from table1 > where... At first you might think I could just combine the select > statements into one statement, but I can't. I need to pass the > results of each separate select statement into distinct variables and > return them together to the application. I'd suggest reading one of the items on the set returning functions: http://www.varlena.com/GeneralBits/26.html http://techdocs.postgresql.org/guides/SetReturningFunctions ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] SECURITY DEFINER changes CURRENT_USER?
Hi Chris, You want to use "session_user". I would expect this to change the password of the user currently logged in but instead it changes MY password. Evidently when a function is called which is set to SECURITY DEFINER, it changes the context of the current user. The CURRENT_USER then returns the name of the definer rather than the invoker of the function. So this being said-- are there any workarounds that don't allow anyone to change anyone else's password? Cheers ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] changing an update via rules
On 15 Jul 2003, Lauren Matheson wrote: > Hello, > > I am having difficulty setting an on update rule which seems to be > caught in a recursive loop. > > Context is a table with three columns assigning users to groups with the > third column being boolean to flag the primary group. I would like to > set an update rule to enforce one primary group. Any suggestions on how > to do this, or exactly how the code is getting trapped? My code is Rules are like macro rewrites and the WHERE clauses will not help to break the recursive loop. I think you may need to either use a trigger or something like a view so that the actual action happens on a different table. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] min() and NaN
On Tue, 15 Jul 2003, Michael S. Tibbetts wrote: > Hi, > > I have a table containing a double precision column. That column > contains at least one judiciously placed NaN. > > I'd expect the aggregate function min() to return the minimum, valid > numeric value. Instead, it seems to return the minimum value from the > subset of rows following the 'NaN'. This appears to be a bug in the min (and presumably max) aggregate function for floats. AFAICT It should use the same logic as the < (or >) comparison operator for the type (SQL92 6.5 GR2b iii I believe), but it's just doing a < (or >) in C on the two arguments which isn't the same. Changing it to follow those rules would give 2.718 for min and it looks like NaN for max. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
