Re: [SQL] slow DELETE queries
On Thursday 20 June 2002 05:01 pm, you wrote: > On Thu, 20 Jun 2002 15:23:53 +0200, Denis <[EMAIL PROTECTED]> wrote: > >I traced the queries slowing it all down to this snippet in the debug log: > >DELETE FROM phpbb_search_wordlist WHERE word_id IN ( > >SELECT word_id FROM phpbb_search_wordmatch WHERE word_id IN ( > >SELECT word_id FROM phpbb_search_wordmatch WHERE post_id IN (70535) > >GROUP BY word_id) GROUP BY word_id HAVING COUNT(word_id) = 1) Hi Manfred, Thanks for your response. > IN is known to be problematic; try to use EXISTS or =, wherever > possible. Can you rewrite your innermost where clause to WHERE > post_id = 70535? I will try out patching the DB abstraction layer to use this if indeed the query allows it. (I am unsure whether this query will sometimes have more IDs inside the IN. > Also create an index on phpbb_search_wordmatch.post_id. I will try this too ! > If it's still too slow, give us some more information: > Is word_id unique in phpbb_search_wordlist? > Is (post_id, word_id) unique in phpbb_search_wordmatch? > How many rows are in your tables? Here is the information : word_id is indeed unique in phpbb_search_wordlist. (post_id, word_id) should be unique in phpbb_search_wordmatch if the application is correctly written. It is supposed to serve as a relational lookup table for resolving search queries into a list of words with matching words inside. Rows in related tables : phpbb_search_wordmatch : 2907191 phpbb_search_wordlist : 118306 phpbb_posts : 70953 VACUUM, VACUUM ANALYZE is performed nightly, maybe this would help to do more often ? -- Denis Braekhus - ABC Startsiden AS http://www.startsiden.no ---(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] Porting from PL/SQL to PLPGSQL
Hi.. In postgres, you have two options.. >From PSQL prompt, you can use " \i filename " to process the file.. OR from PSQL prompt, you can directly instruct to execute that file like.. psql ... --filename <> Basically, when "&&" is specified in PL/SQL scripts, it prompts the user to enter its value. In Postgres, we do not have such option (so far i know). So, you can replace those with your desired values directly. Postgres supports Array type too. Although i haven't used it anyday.. you can try it out. HTH Denis - Original Message - From: "Jomon Skariah" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, August 20, 2003 3:15 PM Subject: [SQL] Porting from PL/SQL to PLPGSQL > > Hi All, > > We are in the process of migrating of our application from Oracle to > PostGreSQL. > > > we are facing a few problems with PL/SQL Code.. > > > 1) In Oracle sqlplus we can run sql script files as @script_name; > How do we do the same in PostGres. > Also is there any replacement for "&&" in PostGres ? > > > Eg: > CREATE USER CATALOG > IDENTIFIED BY &ORA_PASSWORD > DEFAULT TABLESPACE &DFLT_TABLESPACE > TEMPORARY TABLESPACE &TEMP_TABLESPACE > > > 2) In PostGres a function can not take more 16 arguments.We have some > procedures which are taking more than > 16 arguements.So how can we convert them into PostGres. > > > > Can anyone give some valuable suggestions.. > > > Regards > > Joe. > > > > > > > ---(end of broadcast)--- > TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] how to preserve \n in select statement
Hi Richard.. If your users are required to fire only SELECT and no DML, you can do the following: BEGIN; execute the statements given by user ROLLBACK; This will not affect your SELECT and also if any malicious user gives DELETE statement, that will not have any impact too.. HTH Thanx Denis - Original Message - From: "Richard Huxton" <[EMAIL PROTECTED]> To: "Matt Van Mater" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Monday, December 22, 2003 2:34 PM Subject: Re: [SQL] how to preserve \n in select statement > On Saturday 20 December 2003 17:58, Matt Van Mater wrote: > > > > I readily admit that I didn't read much into it, but I think you are > > mistaken. If you look at the following links you will see that postgresql > > definitely had a vulnerability that allowed sql injection through the use > > of escape characters. I was simply saying that this behavior might be a > > way of preventing that from happening again. > > http://www.cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2002-0802 > > http://cert.uni-stuttgart.de/doc/postgresql/escape/ > > Ah - this "vulnerability" is still there - and, in fact is in every database > ever produced. The issue is that applications using untrusted data to build a > query string can be duped by a malicious user. > > So - say you have a query template: > SELECT * FROM recent_news WHERE topic='?' > > If a malicious user supplies the string: '; DELETE FROM user_logins; SELECT ' > then you will have the resulting query string: > SELECT * FROM recent_news WHERE topic=''; DELETE FROM user_logins; SELECT '' > > If you allow multiple queries in one string, there is no way to prevent this. > If you disallow multiple queries, there are still attacks that are possible. > > This is why it is vital to parse and validate user input. If you are asking > for an integer, check that it is. If you are asking for a string, quotes etc. > need to be escaped (AFAICT the stuttgart url describes a function that lets > you do this - you still need to call it). > > The first URL seems to deal with an old bug that meant a cunning attacker > could bypass your escaping. It is still vital that the application (or some > middle layer) validates and checks all untrusted (and preferably all trusted) > input. All standard database layers (Perl/PHP/Java, whatever) supply tools > for this. > > Your particular issue with \n is just down to PG's standard string parsing - > not really related. > -- > Richard Huxton > Archonet Ltd > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faqs/FAQ.html ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] Initially Deffered - FK
Hi all, I am using : PostgreSQL 7.2.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2 20020903 (Red Hat Linux 8.0 3.2-7) I am facing strange problem.. I have created two tables: create table contact (id int constraint contact_pk primary key, name text ); create table address (id int constraint address_fk references contact(id) on delete cascade initially deferred, city text, pin text); Lets.. insert few data in it.. insert into contact values (1, 'Denis'); insert into contact values (2, 'Anand'); insert into contact values (3, 'Debatosh'); insert into contact values (4, 'Pradeep'); insert into address values (1,'Howrah','711102'); insert into address values (2,'Kolkata','71'); insert into address values (3,'Jadavpur','75'); insert into address values (4,'Mumbai','42'); Now, below gives me the correct result. select * from contact; select * from address; acedg=> select * from contact; select * from address; id | name +-- 1 | Denis 2 | Anand 3 | Debatosh 4 | Pradeep (4 rows) id | city | pin +--+ 1 | Howrah | 711102 2 | Kolkata | 71 3 | Jadavpur | 75 4 | Mumbai | 42 (4 rows) BUT, the problem starts when i issue the following set of DMLs in transaction: begin; delete from contact where id=1; insert into contact values (1, 'Denis'); delete from address where id=1;/* this is not required.. but my app.fires. Should not have any impact */ insert into address values (1,'Howrah','711102'); end; It gives me the result: acedg=> select * from contact; select * from address; id | name +-- 2 | Anand 3 | Debatosh 4 | Pradeep 1 | Denis (4 rows) id | city | pin +--+ 2 | Kolkata | 71 3 | Jadavpur | 75 4 | Mumbai | 42 (3 rows) Where is my lastly inserted row ?? i.e. insert into address values (1,'Howrah','711102'); I have tested the same in ORACLE, and it works fine (i.e. both table has 4 records). It is BUG or !!! Pl. help. Thanx Denis ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] Initially Deffered - FK
Hi all, I am using : PostgreSQL 7.2.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2 20020903 (Red Hat Linux 8.0 3.2-7) I am facing strange problem.. I have created two tables: create table contact (id int constraint contact_pk primary key, name text ); create table address (id int constraint address_fk references contact(id) on delete cascade initially deferred, city text, pin text); Lets.. insert few data in it.. insert into contact values (1, 'Denis'); insert into contact values (2, 'Anand'); insert into contact values (3, 'Debatosh'); insert into contact values (4, 'Pradeep'); insert into address values (1,'Howrah','711102'); insert into address values (2,'Kolkata','71'); insert into address values (3,'Jadavpur','75'); insert into address values (4,'Mumbai','42'); Now, below gives me the correct result. select * from contact; select * from address; acedg=> select * from contact; select * from address; id | name +-- 1 | Denis 2 | Anand 3 | Debatosh 4 | Pradeep (4 rows) id | city | pin +--+ 1 | Howrah | 711102 2 | Kolkata | 71 3 | Jadavpur | 75 4 | Mumbai | 42 (4 rows) BUT, the problem starts when i issue the following set of DMLs in transaction: begin; delete from contact where id=1; insert into contact values (1, 'Denis'); delete from address where id=1;/* this is not required.. but my app.fires. Should not have any impact */ insert into address values (1,'Howrah','711102'); end; It gives me the result: acedg=> select * from contact; select * from address; id | name +-- 2 | Anand 3 | Debatosh 4 | Pradeep 1 | Denis (4 rows) id | city | pin +--+ 2 | Kolkata | 71 3 | Jadavpur | 75 4 | Mumbai | 42 (3 rows) Where is my lastly inserted row ?? i.e. insert into address values (1,'Howrah','711102'); I have tested the same in ORACLE, and it works fine (i.e. both table has 4 records). It is BUG or !!! Pl. help. Thanx Denis ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Initially Deffered - FK
Hi Stephan, Thanks for your reply. But, you will agree that result should be same JUST BEFORE and JUST AFTER commit ( assuming no one is working on the database and i am the only user connected.) Till, the commit ( or end ) is issued, if you query ADDRESS, you will get 4 rows. This is expected result. But, just issue commit and see, the result gets changed !! Is this behaviour rectified / changed in later release of PG (say 7.3 or 7.4) ? Any help will be appreciated. Thanx Denis - Original Message - From: "Stephan Szabo" <[EMAIL PROTECTED]> To: "Denis" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Friday, January 16, 2004 10:48 AM Subject: Re: [SQL] Initially Deffered - FK > > On Fri, 16 Jan 2004, Denis wrote: > > create table contact (id int constraint contact_pk primary key, name > > text ); > > create table address (id int constraint address_fk references contact(id) on > > delete cascade initially deferred, > >city text, > >pin text); > > > > Lets.. insert few data in it.. > > > > insert into contact values (1, 'Denis'); > > insert into contact values (2, 'Anand'); > > insert into contact values (3, 'Debatosh'); > > insert into contact values (4, 'Pradeep'); > > > > insert into address values (1,'Howrah','711102'); > > insert into address values (2,'Kolkata','71'); > > insert into address values (3,'Jadavpur','75'); > > insert into address values (4,'Mumbai','42'); > > > > Now, below gives me the correct result. > > > > select * from contact; select * from address; > > > > acedg=> select * from contact; select * from address; > > id | name > > +-- > > 1 | Denis > > 2 | Anand > > 3 | Debatosh > > 4 | Pradeep > > (4 rows) > > > > id | city | pin > > +--+ > >1 | Howrah | 711102 > >2 | Kolkata | 71 > >3 | Jadavpur | 75 > >4 | Mumbai | 42 > > (4 rows) > > > > BUT, the problem starts when i issue the following set of DMLs in > > transaction: > > > > begin; > > delete from contact where id=1; > > insert into contact values (1, 'Denis'); > > delete from address where id=1;/* this is not required.. but my > > app.fires. Should not have any impact */ > > insert into address values (1,'Howrah','711102'); > > end; > > > > It gives me the result: > > > > acedg=> select * from contact; select * from address; > > id | name > > +-- > >2 | Anand > >3 | Debatosh > >4 | Pradeep > >1 | Denis > > (4 rows) > > > > id | city | pin > > +--+ > >2 | Kolkata | 71 > >3 | Jadavpur | 75 > >4 | Mumbai | 42 > > (3 rows) > > > > Where is my lastly inserted row ?? i.e. > > insert into address values (1,'Howrah','711102'); > > Definitional difference. We currently treat a > request to defer the constraint to mean defer > referential actions as well, thus the inserted > address is removed when the on delete cascade > occurs after it at transaction end. Noone's > been entirely sure whether this is correct > or not per spec as I remember. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Left joins with multiple tables
Hi Colin, Try select id, name, a.field1, b.field2, c.field3 from people p left outer join a on (a.person_id = p id) left outer join b on (b.person_id = p.id) left outer join c on (c.person_id = p.id); HTH Denis - Original Message - From: "Colin Fox" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Saturday, January 17, 2004 8:00 AM Subject: [SQL] Left joins with multiple tables > Hi, all. > > I've got a bit of a problem here. I have 4 tables - people, a, b, c (not > the original names). > > For each person in the people table, they may or may not have a record in > a, may or may not have a record in b, and may or may not have a record in > c. > > Handling the first table (a) is easy: > > select id, name > from people p left outer join a on a.person_id = p id; > > But I'd like to be able to do something like: > > select > id, name, a.field1, b.field2, c.field3 > from > people p left outer join a on a.person_id = p id, > people p left outer join b on b.person_id = p.id, > people p left outer join c on c.person_id = p.id; > > Naturally you can't repeat the 'people p' clause 3 times, but is there > some other syntax that would let me do this? > > Thanks! > cf > > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Initially Deffered - FK
Hi Stephan, Thanks for your reply. Will have to work on its workaround... Denis - Original Message - From: "Stephan Szabo" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Monday, January 19, 2004 4:03 PM Subject: Re: [SQL] Initially Deffered - FK > > On Fri, 16 Jan 2004 [EMAIL PROTECTED] wrote: > > > Thanks for your reply. > > > > But, you will agree that result should be same JUST BEFORE and JUST AFTER > > commit ( assuming no one is working on the database and i am the only user > > connected.) > > If you use the definition we're using now, then no, the above is not true. > The row would still exist just before commit because the on delete action > has not happened. The row would not exist just after commit because the > on delete action now has. Whether this definition is correct per spec is > hard to say, but there've been arguments on the subject in the past that > have generally ended without a firm understanding of the specs intention. > > > > ---(end of broadcast)--- > TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Database diagram
Hi Ganesan, You can try ERWin (by CA). It creates Logical data model of the database. (though.. i haven't used it..) HTH Thanx Denis - Original Message - From: "Ganesan Kanavathy" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Tuesday, January 20, 2004 12:08 PM Subject: [SQL] Database diagram > I have a postgres database with many tables. > > How do I create database diagram? Are there any free tools available to > create database diagram from pgsql database? > > Regards, > Ganesan > > > > > ---(end of broadcast)--- > TIP 8: explain analyze is your friend ---(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] max timestamp
Hi Michael, Try this.. ace=> create table test( mytime timestamp ); CREATE ace=> insert into test values (now() ); INSERT 1823542 1 ace=> insert into test values (now() ); INSERT 1823543 1 ace=> insert into test values (now()-1); INSERT 1823544 1 ace=> insert into test values (now()-1); INSERT 1823545 1 ace=> insert into test values (now()-2); INSERT 1823546 1 ace=> select * from test; mytime -- 2004-02-16 14:27:15.936368+05:30 2004-02-16 14:27:20.888205+05:30 2004-02-15 00:00:00+05:30 2004-02-15 00:00:00+05:30 2004-02-14 00:00:00+05:30 (5 rows) ace=> select to_char(mytime,'dd-mm-'),max(mytime) from test group by 1; to_char | max +-- 14-02-2004 | 2004-02-14 00:00:00+05:30 15-02-2004 | 2004-02-15 00:00:00+05:30 16-02-2004 | 2004-02-16 14:27:20.888205+05:30 (3 rows) HTH Thanx Denis - Original Message - From: "Michael Sterling" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, February 10, 2004 11:44 PM Subject: [SQL] max timestamp > i'm trying to get the max time stamp, from each day, of a range of > dates, not just the max time stamp for the complete range dates but > for each day. > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faqs/FAQ.html ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Field list from table
To get the list.. use SELECT a.attname as "Columns" FROM pg_attribute a, pg_class c WHERE c.relname = '' AND a.attnum > 0 AND a.attrelid = c.oid ORDER BY a.attnum Thanx Denis - Original Message - From: "Jan Pips" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, February 26, 2004 3:21 PM Subject: [SQL] Field list from table > How can I, using SELECT, get the full list of fields from a given table? > > Pips > > > > ---(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 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] transaction
Hi, You can achieve this by: 1. Create a new table 2. Insert the data in this. 3. Write a trigger on this table 4. In trigger issue UPDATE and check whether it updated any records. If NO, fire INSERT. ( here, i am updating first and inserting.. just reverse ) The code looks like: update tempxitag set qty = qty + nqty where ccod = cccod GET DIAGNOSTICS nFound = ROW_COUNT; If nFound = 0 then insert into tempxitag( ccod, qty) values (cccod, nqty ); End if; HTH Denis - Original Message - From: Bruno Wolff III <[EMAIL PROTECTED]> To: H.J. Sanders <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Wednesday, April 21, 2004 7:30 AM Subject: Re: [SQL] transaction > On Tue, Apr 20, 2004 at 21:14:48 +0200, > "H.J. Sanders" <[EMAIL PROTECTED]> wrote: > > > > Hello list. > > > > We are migrating from Informix to PSQL. > > > > In Informix we used to do: > > > > - BEGIN WORK > > > > - INSERT ROW > > > > - IF FAILED THEN UPDATE ROW > > > > - COMMIT WORK > > > > > > In PSQL this does not seem to work because after the first error (the > > insert) > > everything is omitted. > > > > Has someone found a solution for this (we have 1000's of this type). > > This question has come up a few times over the last year and there isn't > a great answer. Locking the table is a simple solution, but can lead to > problems because of contention. > > ---(end of broadcast)--- > TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Syntax for cmd to EXEC...how many quotes?
Try (to solve string terminating error ): sql_string := ''INSERT INTO temp_table ( view_name, row_count ) SELECT '' || r_rec.viewname || '', count(*) FROM '' || r_rec.viewname || '' ; '' ; BUT, you will be needing to put view_name in Quote too... try it yourself... HTH Denis - Original Message - From: David B <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, April 21, 2004 4:54 AM Subject: [SQL] Syntax for cmd to EXEC...how many quotes? > Folks, > > This is driving me crazy...I'm sure it's possible but that I am getting the > #quotes wrong in some way... > I keep getting unterminated string errors...now matter how many quotes I > use. > > I have a FN that I want to loop through all views and populate a table with > a count(*) from each views. > > To do it I'm doing a LOOP around all views...something like: > > FOR r_rec IN SELECT viewname from pg_views > LOOP > > sql_string := 'INSERT INTO temp_table ( view_name, row_count ) SELECT > ''' || r_rec.viewname || ''', count(*) FROM ' || r_rec.viewname || ' ; ' ; > > EXEC sql_string ; > > END LOOP ; > > END ; > > > Building that sql_string is the problem. > Any thoughts folks? > > -D > --- > Outgoing mail is certified Virus Free. > Checked by AVG anti-virus system (http://www.grisoft.com). > Version: 6.0.648 / Virus Database: 415 - Release Date: 3/31/2004 > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > >http://archives.postgresql.org ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Order by YYYY MM DD in reverse chrono order trouble
Hi,
If you want to SORT descending considering multiple column, you need to
spefify DESC after each column.
Default is ASC.
So, your present sorting is ASC, ASC and DESC
You can specify
1 DESC, 2 DESC, 3 DESC
HTH
Denis
- Original Message -
From: <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, April 21, 2004 4:00 PM
Subject: [SQL] Order by MM DD in reverse chrono order trouble
> Hello,
>
> I am trying to select distinct dates and order them in the reverse
> chronological order. Although the column type is TIMESTAMP, in this
> case I want only , MM, and DD back.
>
> I am using the following query, but it's not returning dates back in
> the reverse chronological order:
>
> SELECT DISTINCT
> date_part('year', uu.add_date), date_part('month', uu.add_date),
> date_part('day', uu.add_date)
>
> FROM uus INNER JOIN ui ON uus.user_id=ui.id INNER JOIN uu ON
> ui.id=uu.user_id
> WHERE uus.x_id=1
>
> ORDER BY
> date_part('year', uu.add_date), date_part('month', uu.add_date),
> date_part('day', uu.add_date) DESC;
>
>
> This is what the above query returns:
>
> date_part | date_part | date_part
> ---+---+---
> 2004 | 2 | 6
> 2004 | 4 |20
> (2 rows)
>
>
> I am trying to get back something like this:
> 2004 4 20
> 2004 4 19
> 2004 2 6
> ...
>
> My query is obviously wrong, but I can't see the mistake. I was
> wondering if anyone else can see it. Just changing DESC to ASC, did
> not work.
>
> Thank you!
> Otis
>
>
> ---(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] Can someone tell me why this statement is failing?
Hi..
Your END_TIME_MINUTES condition fails..
1082377320 <= 1082375100
HTH.
Denis
- Original Message -
From: P A <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Tuesday, April 20, 2004 2:01 AM
Subject: [SQL] Can someone tell me why this statement is failing?
> Can anyone tell me why this SQL statement is not
> matching with the row from the table below? I think
> I'm going mad!
>
> #
> SQL Statement
> #
>
> SELECT * FROM t_bell_schedule WHERE calendar_day =
> '2004-04-12' AND start_time_minutes >= '1082374200'
> AND end_time_minutes <= '1082375100';
>
> #
> Current DB Data to match
> #
>
> calendar_day | period_letter | start_time_minutes |
> end_time_minutes
> --+---++--
> 2004-04-12 | B | 1082374440 |
> 1082377320
>
> #
> DB Structure
> #
>
>Column |Type |
>Modifiers
> +-+---
-
> bell_schedule_uid | integer |
> not null default
> nextval('public.t_bell_schedule_bell_schedule_uid_seq'::text)
> calendar_day | date|
> period_letter | character varying(4)|
> period | character varying(4)|
> start_time | time without time zone |
> end_time | time without time zone |
> total_minutes | integer |
> activestatus | integer |
> datecreated| timestamp without time zone |
> datemodified | timestamp without time zone |
> start_time_minutes | integer |
> end_time_minutes | integer |
>
>
> Cheers,
> Pete
>
>
>
>
>
> __
> Do you Yahoo!?
> Yahoo! Photos: High-quality 4x6 digital prints for 25¢
> http://photos.yahoo.com/ph/print_splash
>
> ---(end of broadcast)---
> TIP 4: Don't 'kill -9' the postmaster
---(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] ORDER BY TIMESTAMP_column ASC, NULL first
Hi Rod,
Try this
ace=> create table test(name text, age int );
CREATE
ace=> insert into test values ('Denis',26);
INSERT 1823531 1
ace=> insert into test values (null,26);
INSERT 1823532 1
ace=> select * from test order by name;
name | age
---+-
Denis | 26
| 26
(2 rows)
ace=> select * from test order by coalesce(name,'');
name | age
---+-
| 26
Denis | 26
HTH
Thanx
Denis
- Original Message -
From: "Rod Taylor" <[EMAIL PROTECTED]>
To: "Fredrik Wendt" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Monday, February 16, 2004 12:32 AM
Subject: Re: [SQL] ORDER BY TIMESTAMP_column ASC, NULL first
> On Thu, 2004-02-12 at 05:06, Fredrik Wendt wrote:
> > Hi!
> >
> > I read posts telling me that NULL values are considered greater than
> > non-null values. Fine. Is there a way to explicitly reverse this?
>
> ORDER BY column IS NOT NULL, column ASC;
>
>
> ---(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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Are circular REFERENCES possible ?
Hello ! I have a case where I wanted to do circular REFERENCES, is this impossible ? Just an example where it would be useful : We deliver to the *shops* of our *customers*. We have therefore two tables : - customers (enterprise, financial information, and so on...) - shop (with a name, street, phone number, name of manager) Now, each shop REFERENCES a customer so that we know to which customer belongs a shop. AND, each customer has a DEFAULT shop for deliveries, i.e. most customers only have one shop, or a main shop and many small ones. Therefore a customer should REFERENCES the 'main' or 'default' shop. Which leads to : CREATE TABLE shops ( id_shop SERIAL PRIMARY KEY, id_cust integer REFERENCES customers, ...) CREATE TABLE customers ( id_cust SERIAL PRIMARY KEY, id_defaultshop integer REFERENCES shops, ...) But this doesn't work ! Postgres complains like "ERROR: Relation 'customers' does not exist" when creating 'shops'. Someone told me I should create a third table, ok, but in this case I loose the total control about my logic... Do you have a suggestion ? Thanks a lot in advance ! Denis ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] URGENT ! Nouveau virus
Hello ! TRES URGENT : N'utilisez PLUS DU TOUT Microsoft Internet Explorer pour l'instant, à la place vous pouvez utiliser Netscape (http://www.netscape.com) Infos peu claires : http://slashdot.org/articles/01/09/18/151203.shtml En effet, un nouveau virus infecte des sites web, et à cause d'un trou de sécurité (ENCORE) dans le browser de Microsoft, si vous visitez un site qui a été touché votre ordinateur sera infecté par de TRES NOMBREUX VIRUS D'UN SEUL COUP. Je pense que d'ici un jour ou deux les antivirus pourront combattre cela et qu'on aura plus d'informations, mais en attendant FERMEZ INTERNET EXPLORER ET NE L'OUVREZ PLUS Bonne soirée ! Denis Bucher ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] URGENT ! Nouveau virus
Hello ! Ok... I wrote a small text against that virus just in case you need it ! http://www.horus.ch/virus180901.txt A bientôt ! Denis Bucher ---(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
[SQL] Epoch extraction
Title: Message When i extract epoch from field (timestamp(0) without time zone) I get the time that is set in that field plus one hour... Is that a bug or am I just missing something Regards, Denis Arh
Re: [SQL] default operator class (PostgreSQL's error?)
I've found a reason! It's some namespace problem - there are other tho name_ops operator classes exist. My becomes third. All are the default for (their) type. And somewhere there is the issue. Renaming my operator class into, say, name_t_ops resolves the problem. Thanks for the info. ---(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] Delete duplicates
How to delete "real" duplicates? id | somthing --- 1 | aaa 1 | aaa 2 | bbb 2 | bbb (an accident with backup recovery...) Regards, Denis Arh - Original Message - From: "Franco Bruno Borghesi" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Sunday, June 22, 2003 11:17 PM Subject: Re: [SQL] Delete duplicates > try this > > DELETE FROM aap WHERE id NOT IN ( >SELECT max(id) >FROM aap >GROUP BY keyword > ); > > > > > > > Hi, > > > > I have a table with duplicates and trouble with my SQL. > > I'd like to keep a single record and remove older duplicates. > > For example below of the 6 recods I'd like to keep records > > 4 and 6. > > > > TABLE: aap > > id | keyword > > +- > > 1 | LEAGUE PANTHERS > > 2 | LEAGUE PANTHERS > > 3 | LEAGUE PANTHERS > > 4 | LEAGUE PANTHERS > > 5 | LEAGUE BRONCOS > > 6 | LEAGUE BRONCOS > > > > Here is my SQL so far, it will select records 1 to 5 instead > > of 1,2,3 and 5 only. > > > > Any help greatly appreciated. I think I need a Group By somewhere in > > there. > > > > select a1.id > > from aap a1 > > where id < ( SELECT max(id) FROM aap AS a2 ) > > AND EXISTS > > ( > > SELECT * > > FROM aap AS a2 > > WHERE a1.keyword = a2.keyword > > ) > > > > Regards > > Rudi. > > > > ---(end of > > broadcast)--- TIP 1: subscribe and unsubscribe > > commands go to [EMAIL PROTECTED] > > > > > ---(end of broadcast)--- > TIP 8: explain analyze is your friend > > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Very strange 'now' behaviour in nested triggers.
In short, the idea this example is to test for is to split a
comma-separated value of some text attribute (given to the INSERT
operator) and then insert a row for each of the parts of that text
value. I've tried to do this thru a nested triggers approach.
create
table xxx (
s text,
t timestamp
default 'now'
);
create
function xxx () returns trigger
language plpgsql
as '
declare
tail text;
head integer;
begin
tail:= substring(new.s, \'[^,]+$\');
head:= length(new.s)-
length(tail) -1;
if head > 0 then
insert into xxx values (
substring(new.s for head)
--,new.t
);
end if;
new.s:= trim(tail);
raise notice \'"%"\', new.s;
raise notice \'"%"\', new.t;
return new;
end;
';
create
trigger xxx
before insert on xxx
for each row execute procedure
xxx ();
Then:
zzz=> INSERT INTO xxx VALUES (' a ,b, c, d , x');
NOTICE: "a"
NOTICE: "2003-07-26 19:17:26.514217"
NOTICE: "b"
NOTICE: "2003-07-26 19:17:26.514217"
NOTICE: "c"
NOTICE: "2003-07-26 19:17:26.514217"
NOTICE: "d"
NOTICE: "2003-07-26 19:17:26.514217"
NOTICE: "x"
NOTICE: "2003-07-26 19:17:26.514217"
INSERT 223886 1
zzz=> INSERT INTO xxx VALUES (' a ,b, c, d , x');
NOTICE: "a"
NOTICE: "2003-07-26 19:17:26.514217"
NOTICE: "b"
NOTICE: "2003-07-26 19:17:26.514217"
NOTICE: "c"
NOTICE: "2003-07-26 19:17:26.514217"
NOTICE: "d"
NOTICE: "2003-07-26 19:17:26.514217"
NOTICE: "x"
NOTICE: "2003-07-26 19:17:28.300914"
INSERT 223891 1
zzz=> INSERT INTO xxx VALUES (' a ,b, c, d , x');
NOTICE: "a"
NOTICE: "2003-07-26 19:17:26.514217"
NOTICE: "b"
NOTICE: "2003-07-26 19:17:26.514217"
NOTICE: "c"
NOTICE: "2003-07-26 19:17:26.514217"
NOTICE: "d"
NOTICE: "2003-07-26 19:17:26.514217"
NOTICE: "x"
NOTICE: "2003-07-26 19:17:30.948737"
INSERT 223896 1
zzz=> SELECT * from xxx;
s | t
---+
a | 2003-07-26 19:17:26.514217
b | 2003-07-26 19:17:26.514217
c | 2003-07-26 19:17:26.514217
d | 2003-07-26 19:17:26.514217
x | 2003-07-26 19:17:26.514217
a | 2003-07-26 19:17:26.514217
b | 2003-07-26 19:17:26.514217
c | 2003-07-26 19:17:26.514217
d | 2003-07-26 19:17:26.514217
x | 2003-07-26 19:17:28.300914
a | 2003-07-26 19:17:26.514217
b | 2003-07-26 19:17:26.514217
c | 2003-07-26 19:17:26.514217
d | 2003-07-26 19:17:26.514217
x | 2003-07-26 19:17:30.948737
(15 rows)
So, all the timestamps except those for the last 'x' field are the
same! These "the same" timestamps are really the timestamp of the
first top-level INSERT. And the timestamps for the last field of the
comma-separated string are the correct things. This last field is
cultivated by the top-level trigger's call.
If to set new.t for nested triggers explicitly (commented in the
trigger code above), then all will be ok. But this is not a cure, of
course.
So, what does it mean? Is this a bug (PostgreSQL 7.3.2)? Or do I
misunderstand something?
Thanks in advance.
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Very strange 'now' behaviour in nested triggers.
On Sat, Jul 26, 2003 at 03:14:16PM +0100, Richard Huxton wrote: > On Saturday 26 July 2003 14:39, Denis Zaitsev wrote: > > In short, the idea this example is to test for is to split a > > comma-separated value of some text attribute (given to the INSERT > > operator) and then insert a row for each of the parts of that text > > value. I've tried to do this thru a nested triggers approach. > > I'm not sure I'd use this approach for very long strings Of course not a very deep recursion, the strings are expected to consist of less than 10 pieces. > Not exactly a bug. The crucial thing is that 'now' gets evaluated when the > query is parsed and the plan built. For the main INSERT that's at the start > of the transaction (which is what you want). > > For the trigger function, what happens is the plan for that insert gets > compiled the first time the function is called and 'now' gets frozen. Ok, thanks a much. I've realized... > Solution: make the default now() or CURRENT_TIMESTAMP and all will be as you > expect. > > PS - I think this is mentioned in the manuals somewhere, but it's not > surprising you missed it. Interesting example. As I remember, namely 'now' is mentioned in the manuals, as the best approach to keep the same value thru the whole transaction. That is why I used it here. For now I've tested that now() does the thing. Why? I remember that now() is changing thru the transaction, just showing the current time... ---(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] Very strange 'now' behaviour in nested triggers.
On Sat, Jul 26, 2003 at 10:31:44AM -0400, Tom Lane wrote: > Denis Zaitsev <[EMAIL PROTECTED]> writes: > > create table xxx ( > > s text, > > t timestamp > > default 'now' > > ); > > That's a dangerous way to define the default --- 'now' is taken as a > literal of type timestamp, which means it will be reduced to a timestamp > constant as soon as a statement that requires the default is planned. > You lose in plpgsql because of plan caching, but you'd also lose if you > tried to PREPARE the insert command. Example: Aaa... So, the INSERT inside a trigger will use the 'now' for the time this trigger is compiled (i.e. called first time)? Do I understand right? And the only outer trigger uses the right 'now' as its value goes from the top-level INSERT... Thank you very much. By the way, do you think this method with nested triggers has some 'moral weakness' vs. just cycling left-to-right on the comma-separated string in the 'do instead' rule for some view of xxx? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Very strange 'now' behaviour in nested triggers.
On Sun, Jul 27, 2003 at 08:47:16AM +0100, Richard Huxton wrote: > > No- 'now',now() and CURRENT_TIMESTAMP all stay fixed during a transaction. > The one that changes is timeofday() I think. See the "Functions and > Operators" section for details. Yes, indeed... Documentation describes this. And I haven't found anything about 'now' that I used to say... I don't know why (my memory leak?) :) Thanks. BTW, this text is at the bottom of the "Date/Time Functions and Operators" section (functions-datetime.html): SELECT CURRENT_TIMESTAMP; SELECT now(); SELECT TIMESTAMP 'now'; Note: You do not want to use the third form when specifying a DEFAULT clause while creating a table. The system will convert now to a timestamp as soon as the constant is parsed, so that when the default value is needed, the time of the table creation would be used! ... It's nearly what you have written about. But I want to note phrase. Should it be fixed there? ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] NEW and a subselect in a rule
So, I met such a problem: it's impossible to use NEW in a subselect used in a (non-select) rule. The error is: . Is this a way to do that newertheless (without using of a function, of course)? Thanks in advance. ---(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] Nonexistent NEW relation in some places of rules
In general, the task I'm trying to solve is to make a multiply inserts for a table on the one only insert for a view. It should be noted about the method of producing these multiply rows, that they depend on the VALUES given to that INSERT. So, the trivialized schema is: create function produce (text) returns setof text language plpgsql as ' begin return next $1||1; return next $1||2; return next $1||3; return; end; '; create table a (a text); create view b as select a as b from a; create rule b as on insert to b do instead insert into a select * from produce (new.b); And I get psql:zhoppa.sql:21: ERROR: Relation "*NEW*" does not exist when I feed this to psql... So, what is wrong in using NEW right from the FROM? Thanks in advance. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Very strange 'now' behaviour in nested triggers.
On Sun, Jul 27, 2003 at 11:49:10AM -0400, Tom Lane wrote: > I put up a proposal in pgsql-hackers to change this behavior: > http://archives.postgresql.org/pgsql-hackers/2003-07/msg00818.php > If we made that change then the "wrong" way of defining the default > would fail in an obvious fashion --- the 'now' would get reduced to a > particular time immediately at CREATE TABLE. Doubtless this would annoy > some people, but the "right" way of defining the default isn't really > any harder, and it would save folks from getting burnt in corner cases, > like you were. > > Any comments? The proposed behavious promises to be "strict, simple and well-defined". The current one definitely tends to have "dirty and non-obvious side effects here and there". I think the new behaviour should conquer. BTW, the documentation describes this proposed behaviour (but why?), not the current one... ---(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] Nonexistent NEW relation in some places of rules
On Tue, Jul 29, 2003 at 06:32:44PM -0400, Tom Lane wrote: > The problem is that the rule gets expanded into something rather like > > insert into a select ... from new, produce(new.b); > > and we can't support that. If we ever add support for SQL99's > LATERAL(), it might help improve matters. Thanks. I was suspecting something like. Does the presence of this leak mean that such a selects are needed very rarely? Or does it mean that some well-known workaround exists? ---(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] NEW and a subselect in a rule
On Thu, Jul 31, 2003 at 10:00:07AM +0200, Christoph Haller wrote: > > > > So, I met such a problem: it's impossible to use NEW in a subselect > > used in a (non-select) rule. The error is: > > exist>. Is this a way to do that newertheless (without using of a > > function, of course)? > > > Could we see the CREATE RULE command causing the error? I've sent the other message (with the Subject: Nonexistent NEW relation...) into the list. The code is there. The problem is already closed (not solved!). Thanks anyway. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] not really SQL but I need info on BLOBs
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Theodore Petrosky wrote: | I am working on a project where the IT department is | pushing really hard to have all the images in the db. | I don't know what the agenda is. I am hopeful to come | up with reasons either why this is good or not good. We have never used BLOB support for images, however one additional feature of using BLOBs vs files on a disk would be having easier setup if multiple servers/clients need to access those images. In the case of using a filesystem you would either have to provide access to the images over a network filesystem or replication, whereas using the DB for storage does not require anything more than access to the DB.. We are using filesystem replication for situations like this (multiple webservers needing access to exactly the same images), however there are multiple other options for this situation. Of course needing to do editing on the images aswell provides additional hurdles. Bottom line seems to me to be that if this is all to be located on one server there is no good reason to not use filesystem storage for images, whereas if you need multiple servers and generally a more complex setup you should weigh the pros to the cons.. Regards - -- Denis -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.2-nr2 (Windows XP) iD8DBQFAnieIvsCA6eRGOOARAiwYAKCeaMfnq35nGoQRixKAsec/+k4kwwCdHy91 EyIqpTqWbZimUFdOjaFdpbI= =Uzfm -END PGP SIGNATURE- ---(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
[SQL] Which SQL command creates ExclusiveLock?
Hi everyone! I have a web application that uses Postgresql on backend. The application performs selects, updates, inserts, and deletes by using Hibernate. Tables contain indexed fields. When I run the following query, SELECT * FROM pg_locks, it shows that some transactions place ExclusiveLock: relation | database | transaction | pid | mode | granted--+--+-+---+-+- 16757 | 16976 | | 22770 | AccessShareLock | t | | 17965163 | 22770 | ExclusiveLock | t According to postgres documentation, all update operations place ROW EXCLUSIVE MODE locks, and EXCLUSIVE MODE is not automatically acquired by any postgres SQL command. So, which command places ExclusiveLock??? I suspect that indexes can account for this behaviour, but couldn't find anything in the docs. I am also wondering why there is nothing shown in "relation" column. I think it is supposed to display a table or index id or any other object that is being locked. Thanks for help! Dennis
Re: [SQL] mail alert
Hello, Jan Verheyden a écrit : > I was looking in what way it’s possible to alert via mail when some > conditions are true in a database. a) If the alert is not "very urgent" i.e. you can alter some minutes later I would do it like this : 1. Create a function that returns what you need, most importantly if the conditions are met 2. Create a script that does something like "SELECT * FROM function()..." b) If the email should be sent immediately, you could create a perl function in the database, launched by a TRIGGER and launching an "external" script... Therefore, first you have to know the requirements... Denis -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Question about encoding
Hello, I'm sure you already replied tons of questions like this, but I don't understand something. This is the situation : * I have COPY commands from a dump, encoded as UTF8 (special characters are encoded as 2-bytes). * My file contains SET client_encoding = 'UTF8'; * But when I do psql http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Question about encoding
Hello, Peter Eisentraut a écrit : >> Question 1 : >> Is it the expected behavior ? These characters have a SQL_ASCII >> equivalent because I already have them stored in another table of the >> same database > > SQL_ASCII is not the same as ASCII. SQL_ASCII means, take the bytes as > they come. So a 40-character UTF-8 string might indeed be longer than > 40 bytes, which is what SQL_ASCII will look at. > > The best bet is to avoid SQL_ASCII altogether. It's pretty bogus and > inconsistent. OK that's a good point it explains everything. >> Question 2 : >> If yes, then I suppose I should have the database as LATIN1 or UTF8. >> Can I change/convert the encoding of the database ? Or at least of the >> schema (which would be even better) > > Dump, recreate database with right encoding, restore. Ok I did it but postgresql 7.4 was not very nice, the following failed : 1. dump 2. DROP DATABASE; CREATE DATABASE WITH ENCODING UTF8 3. restore And it bugged. In fact the dump was writtent with "SET CLIENT ENCODING=SQL_ASCII but when restoring on the UTF8 databse it failed, I had to change it to SET client_encoding = LATIN1. Anyway, finally everything is working again, I just had to add a SET NAMES LATIN1 to my application because it was ISO and not UTF8 aware. Therefore thanks a lot for your help, it solved all my problems ! Denis -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Problem with return type of function ???
Hello, I have a strange problem, because it worked in a fonction for a table, and now I created the same (?) function for another table and it doesn't work... The function is accepted but at runtime I get : ERREUR: wrong record type supplied in RETURN NEXT CONTEXTE : PL/pgSQL function "hds_bw_find_sn_live" line 26 at return next Does someone maybe knows what it could be ? This is (a part of) my function : > CREATE OR REPLACE FUNCTION rma.find_sn_live (varchar(30)) RETURNS SETOF > rma.serial_number > AS $_$ > DECLARE >serialnumber ALIAS FOR $1; >row RECORD; > BEGIN > > FOR row IN > SELECT * FROM rma.serial_number WHERE sn=serialnumber > LOOP > RETURN NEXT row; > END LOOP; > > END; > $_$ > LANGUAGE plpgsql STRICT; Thanks a lot for any help ! Denis -- 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] Problem with return type of function ???
Richard Huxton a écrit : >> ERREUR: wrong record type supplied in RETURN NEXT >> CONTEXTE : PL/pgSQL function "hds_bw_find_sn_live" line 26 at return next >> >> Does someone maybe knows what it could be ? >> >> This is (a part of) my function : >> >>> CREATE OR REPLACE FUNCTION rma.find_sn_live (varchar(30)) RETURNS SETOF >>> rma.serial_number > > This is a different function. Function "hds_bw_find_sn_live" has the > wrong type for its "return next". Hello, Thanks a lot for your reply : the difference in name is just a mistake in my email but in fact it is the same function, I just renamed it wrong when doing the "cleaning" before posting my email... Denis -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Problem with return type of function ??? (corrected)
Hello, (CORRECTED VERSION OF MY PREVIOUS EMAIL) I have a strange problem, because it worked in a fonction for a table, and now I created the same (?) function for another table and it doesn't work... The function is accepted but at runtime I get : ERREUR: wrong record type supplied in RETURN NEXT CONTEXTE : PL/pgSQL function "find_sn_live" line 26 at return next Does someone maybe knows what it could be ? This is (a part of) my function : > CREATE OR REPLACE FUNCTION rma.find_sn_live (varchar(30)) RETURNS SETOF > rma.serial_number > AS $_$ > DECLARE >serialnumber ALIAS FOR $1; >row RECORD; > BEGIN > > FOR row IN > SELECT * FROM rma.serial_number WHERE sn=serialnumber > LOOP > RETURN NEXT row; > END LOOP; > > END; > $_$ > LANGUAGE plpgsql STRICT; Thanks a lot for any help ! Denis -- 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] Problem with return type of function ???
Hello Richard, Richard Huxton a écrit : >>>> ERREUR: wrong record type supplied in RETURN NEXT >>>> CONTEXTE : PL/pgSQL function "hds_bw_find_sn_live" line 26 at return next >>>> >>>> Does someone maybe knows what it could be ? >>>> >>>> This is (a part of) my function : >>>> >>>>> CREATE OR REPLACE FUNCTION rma.find_sn_live (varchar(30)) RETURNS SETOF >>>>> rma.serial_number >>> This is a different function. Function "hds_bw_find_sn_live" has the >>> wrong type for its "return next". >> Hello, >> >> Thanks a lot for your reply : the difference in name is just a mistake >> in my email but in fact it is the same function, I just renamed it wrong >> when doing the "cleaning" before posting my email... > > In that case - have you changed the definition of table > rma.serial_number since you defined the function? No, I just do the test just after the "CREATE OR REPLACE FUNCTION". I saw somwhere it could be the order of the fields ? Denis -- 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] Problem with return type of function ???
Hello Richard,
Richard Huxton a écrit :
>> I saw somwhere it could be the order of the fields ?
>
> Not if you are doing "SELECT * FROM".
>
> Create an empty test database and a short script to create the table and
> function, insert a couple of rows then call the function. If you wrap
> the whole thing in BEGIN ... ROLLBACK we can change things until we see
> the problem.
>
> The other thing you could try is printing out row before returning it:
> RAISE NOTICE 'row = %', row;
> RETURN NEXT ROW;
> It might be you've not got what you were expecting.
Thanks a lot, good idea...
But it looks good :
> SELECT * FROM rma.test ('19G256259');
> NOTICE: row = (12066602,19G256259,170224,PN6405B,2009-09-22,"FORERUNNER 405
> NOIR",2009-09-22,15090,14748)
> ERREUR: wrong record type supplied in RETURN NEXT
> CONTEXTE : PL/pgSQL function "test" line 12 at return next
>
> \d rma.serial_number
>Table « rma.serial_number »
>Colonne | Type | Modificateurs
> -+---+---
> sn_id | bigint| not null default
> nextval('rma.serial_number_sn_id_seq'::regclass)
> sn | character varying(30) |
> no_client | integer |
> no_art_bw | character varying(11) |
> sn_fc_date | date |
> desc_fr | character varying(40) |
> sn_cm_date | date |
> no_facture | integer |
> no_commande | integer |
Denis
--
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] Problem with return type of function ???
Richard Huxton a écrit :
>>> The other thing you could try is printing out row before returning it:
>>> RAISE NOTICE 'row = %', row;
>>> RETURN NEXT ROW;
>>> It might be you've not got what you were expecting.
>> Thanks a lot, good idea...
>>
>> But it looks good :
>
> Hmm...
>
>>> SELECT * FROM rma.test ('19G256259');
>>> NOTICE: row = (12066602,19G256259,170224,PN6405B,2009-09-22,"FORERUNNER
>>> 405 NOIR",2009-09-22,15090,14748)
>>> ERREUR: wrong record type supplied in RETURN NEXT
>>> CONTEXTE : PL/pgSQL function "test" line 12 at return next
>>>
>>> \d rma.serial_number
>>>Table « rma.serial_number »
>>>Colonne | Type |
>>> Modificateurs
>>> -+---+---
>>> sn_id | bigint| not null default
>>> nextval('rma.serial_number_sn_id_seq'::regclass)
>>> sn | character varying(30) |
>>> no_client | integer |
>>> no_art_bw | character varying(11) |
>>> sn_fc_date | date |
>>> desc_fr | character varying(40) |
>>> sn_cm_date | date |
>>> no_facture | integer |
>>> no_commande | integer |
>
> I was wondering if maybe there was a bug to do with domains or complex
> column types, but there's nothing out of the ordinary here.
Yes...
> OK - can you generate a test script with just CREATE TABLE, CREATE
> FUNCTION, one INSERT and a function-call? I'll try and recreate it here.
> Oh, and what version of PostgreSQL are we talking about?
> Bienvenue dans psql 8.1.17, l'interface interactive de PostgreSQL.
OK I prepared what you asked and I tested it myself before sending.
And I think I've found the problem (but not the solution !) :
When I dump the FUNCTION, I get this :
> CREATE test(character varying) RETURNS SETOF serial_number
instead of this :
> CREATE test(character varying) RETURNS SETOF rma.serial_number
That seems to be some bug in Postgres ?
The problem is then clear, it doesn't take SETOF rma.serial_number but
SETOF public.serial_number
Do you see how we could solve this ? And do you think this is the problem ?
Thanks a lot again for all your help !
Denis
--
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] Problem with return type of function ??? (corrected)
Hello Tom, Tom Lane a écrit : > Denis BUCHER writes: >> I have a strange problem, because it worked in a fonction for a table, >> and now I created the same (?) function for another table and it doesn't >> work... > >> The function is accepted but at runtime I get : > >> ERREUR: wrong record type supplied in RETURN NEXT >> CONTEXTE : PL/pgSQL function "find_sn_live" line 26 at return next > > Does that table have any dropped columns? If you don't remember > whether you ever dropped any columns, a quick look into pg_attribute > will tell you: > select attname from pg_attribute where attrelid = > 'rma.serial_number'::regclass; Yes ! attname -- tableoid cmax xmax cmin xmin ctid sn_id sn no_client no_art_bw sn_fc_date pg.dropped.6 pg.dropped.7 desc_fr sn_cm_date no_facture no_commande (17 lignes) > plpgsql isn't tremendously good with rowtypes that contain dropped > columns. I believe this particular case is fixed in CVS HEAD, but the > patch was a bit invasive and won't get back-ported to existing releases. > The workaround is to drop and recreate the table without any dropped > columns. OH, I see... Thanks a lot, I will try this later today when nobody uses the application... Denis -- 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] Problem with return type of function ??? (corrected)
Hi Tom, Another question : Tom Lane a écrit : > Denis BUCHER writes: >> I have a strange problem, because it worked in a fonction for a table, >> and now I created the same (?) function for another table and it doesn't >> work... > >> The function is accepted but at runtime I get : > >> ERREUR: wrong record type supplied in RETURN NEXT >> CONTEXTE : PL/pgSQL function "find_sn_live" line 26 at return next > > Does that table have any dropped columns? If you don't remember > whether you ever dropped any columns, a quick look into pg_attribute > will tell you: > select attname from pg_attribute where attrelid = > 'rma.serial_number'::regclass; > > plpgsql isn't tremendously good with rowtypes that contain dropped > columns. I believe this particular case is fixed in CVS HEAD, but the > patch was a bit invasive and won't get back-ported to existing releases. > The workaround is to drop and recreate the table without any dropped > columns. To do this it will be a little complicated because of table dependencies... And it could bug again at the next DROP COLUMN... Is there a way to change my function (RETURN SETOF part) to specify the column names/types ? Thanks a lot again Denis -- 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] Problem with return type of function ??? (corrected)
Tom Lane a écrit : > Denis BUCHER writes: >> To do this it will be a little complicated because of table >> dependencies... And it could bug again at the next DROP COLUMN... Is >> there a way to change my function (RETURN SETOF part) to specify the >> column names/types ? > > No, not really. You could maybe un-drop the columns with some manual > surgery on pg_attribute, but it doesn't seem like that's going to lead > to a nice solution. > > If you were really desperate you could try back-porting the patch: > http://archives.postgresql.org/pgsql-committers/2009-08/msg00068.php > but I wouldn't want to vouch for its safety, considering it hasn't > been through a beta test cycle yet. Finally there was no real dependencies of that table, and I've found out that the "dependency" was my function ! Using SETOF (table) makes impossible to DROP the table. Therefore I DROP my function and was able to follow you advice, and it worked perfectly ! Thanks a lot for your help (as well as Richard's) Denis -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] EXISTS
Hi, I would like to know if this this type of statement can be used in Postgresql IF NOT EXISTS (SELECT 1 FROM Table WHERE col1 = 'mystring' ) BEGIN ... END If it can, any idea why I get this error. ** Error ** ERROR: syntax error at or near "IF" SQL state: 42601 Character: 2 Thanks denis woodbury [EMAIL PROTECTED] 450-242-0249 -- 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] EXISTS
Thanks to those who responded, I see PL/PgSQL is the way to go Regards, denis woodbury on 10/11/08 1:32 PM, [NAME] at [ADDRESS] wrote: > Denis Woodbury <[EMAIL PROTECTED]> writes: >> I would like to know if this this type of statement can be used in >> Postgresql > >> IF NOT EXISTS (SELECT 1 FROM Table WHERE col1 = 'mystring' ) >> BEGIN >> ... >> END > > I suspect you are trying to type that directly into SQL. > You need to be using plpgsql in order to use procedural > logic (ie, if/then). > > regards, tom lane -- 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] staggered query?
Hi Try this.. SELECT Col1 , Col2 FROM yourtable WHERE to_number(to_char(col1, 'SS'),'99') / 10 ) in (10,20,30,40,50,00); HTH Denis > - Original Message - > From: Vincent Ladlad <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Wednesday, April 21, 2004 8:23 AM > Subject: [SQL] staggered query? > > > > hi! im new to SQL, and i need to find a solution > > to this problem: > > > > i have a table with two columns, the first column > > is of type timestamp. > > > > the table contains hundreds of thousands of records. > > i need to get all the entries/records at every 10 seconds > > interval. example, given a table: > > > > hh/mm/ss | data > > --- > > 00:00:00 1 > > 00:00:01 2 > > 00:00:02 3 > > 00:00:03 4 > > 00:00:04 5 > > 00:00:05 6 > > 00:00:06 7 > > 00:00:07 8 > > .. > > .. > > > > my query should return: > > 00:00:10 > > 00:00:20 > > 00:00:30 > > (etc) > > > > is this possible? if yes, how do i do it? > > > > thanks! > > > > --- > > Outgoing mail is certified Virus Free. > > Checked by AVG anti-virus system (http://www.grisoft.com). > > Version: 6.0.484 / Virus Database: 282 - Release Date: 5/27/2003 > > > > > > > > ---(end of broadcast)--- > > TIP 4: Don't 'kill -9' the postmaster > > ---(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] Record Lock details
Hi all, I am using PostgreSQL 7.3.1. Is there a data dictionary in Postgres from where i can get the info about locked rows of any table ? If possible the value of those locked record ? My situation is like: I have an ODBC application working on Postgres. There are Master child tables. I want, if a user A is editing any document ( one master and set of child records) in front-end application, the same should not be available to other user for editing. I thought of using SELECT FOR UPDATE. But, in my case, i need to display the name of application user and other details about the locked row. >From which data dictionary, i should query. Any help would be appreciated. Thanx Denis ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
