[SQL] Function to either return one or all records
Hi all, I think I have got a usual problem. I'm asking here, however, because I wonder why it works this way. The problem is to write a fcn that eihter returns all records or just one/none filtered by some expression. For example get a value by id or return all values if the given id is null. For a table like this (id (PK) int | val int ) one would write a function like this: CREATE OR REPLACE FUNCTION getval(integer) RETURNS SETOF id_val_tbl AS $BODY$ select * from id_bal_tbl where ( $1 is null )or (id=$1 ); $BODY$ LANGUAGE 'sql' VOLATILE SECURITY DEFINER; It works fine, however an index is never used (if just one record is requested). The column id has a btree-Index but what aobut it. I'm wondering how this comes and how one can overcome this limit. I read that btree and null values do not like each other very well. And surely, if the Select is transformed reduced to select * from id_bal_tbl where (false)or (id=$1 ); the Index is used! What I don't get is: When expanding parameters in the SQL-function it will evaluate to the statement above. Bevore any data is looked up. Why is that? Thanks again in advance ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Debet-Credit-Balance Calculation
There is an easy way to do this; write a plpgsql set returning function which adds the balance to the last column of the table. That query will always have a cost in both time and memory proportional to the size of the table, and the memory cost may bite you as table size grows... -- Can you give me some example function which adds the balance to the last column of the table. Thanks. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Debet-Credit-Balance Calculation
There is an easy way to do this; write a plpgsql set returning function which adds the balance to the last column of the table. That query will always have a cost in both time and memory proportional to the size of the table, and the memory cost may bite you as table size grows... -- Can you give me some example function which adds the balance to the last column of the table. or other query which same result and more faster Thanks. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Debet-Credit-Balance Calculation
[EMAIL PROTECTED] ("Muhyiddin A.M Hayat") writes:
>> There is an easy way to do this; write a plpgsql set returning
>> function which adds the balance to the last column of the table. That
>> query will always have a cost in both time and memory proportional to
>> the size of the table, and the memory cost may bite you as table size
>> grows...
>> --
>
> Can you give me some example function which adds the balance to the
> last column of the table.
>
> Thanks.
No time, but here is a link to a useful article on SRFs.
Once you know how to do an SRF, you should be able to build the one
you need.
--
(format nil "[EMAIL PROTECTED]" "cbbrowne" "acm.org")
http://www.ntlug.org/~cbbrowne/sap.html
Rules of the Evil Overlord #78. "I will not tell my Legions of Terror
"And he must be taken alive!" The command will be: ``And try to take
him alive if it is reasonably practical.''"
[SQL] Odd error message (" hash indexes do not support whole-index scans")
Shouldn't PostgreSQL behave in a different way in this case? (e.g. just
not use the index):
Table "public.forum_messages"
message_id | integer | not null default
nextval('forum_messages_message_id_seq'::text)
parent_userid | integer |
supersededby| integer | default 0
"forum_messages_pkey" primary key, btree (message_id)
"idx_supersede" hash (supersededby) WHERE (supersededby > 0)
# select m1.message_id,m2.message_id,m1.parent_userid,m2.parent_userid
from forum_messages m1, forum_messages m2 where
m2.supersededby=m1.message_id and m2.supersededby>0;
ERROR: hash indexes do not support whole-index scans
Regards,
Marinos
---(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] Odd error message (" hash indexes do not support whole-index scans")
Marinos Yannikos <[EMAIL PROTECTED]> writes: > Shouldn't PostgreSQL behave in a different way in this case? (e.g. just > not use the index): Good catch. But why are you using a hash index for this? regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] Query question
Hi , I have table that represent a switch activity like this : | date| state | | 2005-04-20 17:00:00 | 0 | | 2005-04-20 17:00:15 | 0 | | 2005-04-20 17:00:30 | 1 | | 2005-04-20 17:00:45 | 1 | | 2005-04-20 17:01:00 | 1 | | 2005-04-20 17:01:15 | 0 | | 2005-04-20 17:01:30 | 0 | | 2005-04-20 17:01:45 | 0 | I want to get the date of each states change but i not a sql expert. Can someone advices me Thanks ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Query question
If you have a row every 15 seconds, the answer is quite easy: SELECT A1.date FROM activity A1 LEFT JOIN activity A2 ON (A2.date=A1.date-'15 secs'::interval) WHERE A1.state<>A2.state OR A2.state IS NULL ORDER BY 1 Now if you don't have a row every 15 seconds, the answer is a bit more complex (at least I couldn't think of an easier solution): SELECT min(TMP2.new_date) FROM ( SELECT DISTINCT TMP.new_date, max(TMP.old_date) AS max_old_date FROM ( SELECT A1.id AS new_id, A1.date AS new_date, A1.state AS new_state, A2.id AS old_id, A2.date AS old_date, A2.state AS old_state FROM activity A1 LEFT JOIN activity A2 ON (A2.dateTMP.new_state OR TMP.old_state IS NULL GROUP BY TMP.new_date ) TMP2 GROUP BY TMP2.max_old_date ORDER BY 1 I've tested both queries on postgreSQL 8 with the data you provided, and they both work. Anyway try them with larger datasets before using them in real life ;-) Hope it helps. Stéphane RIFF wrote: Hi , I have table that represent a switch activity like this : | date | state | | 2005-04-20 17:00:00 | 0 | | 2005-04-20 17:00:15 | 0 | | 2005-04-20 17:00:30 | 1 | | 2005-04-20 17:00:45 | 1 | | 2005-04-20 17:01:00 | 1 | | 2005-04-20 17:01:15 | 0 | | 2005-04-20 17:01:30 | 0 | | 2005-04-20 17:01:45 | 0 | I want to get the date of each states change but i not a sql expert. Can someone advices me Thanks ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Function to either return one or all records
> > CREATE OR REPLACE FUNCTION getval(integer) > RETURNS SETOF id_val_tbl AS > $BODY$ > select * from id_bal_tbl where ( $1 is null )or (id=$1 ); $BODY$ > LANGUAGE 'sql' VOLATILE SECURITY DEFINER; > > > It works fine, however an index is never used (if just one > record is requested). The column id has a btree-Index but > what aobut it. I'm wondering how this comes and how one can > overcome this limit. The reason why the query worked as plain query may come from the fact that NULL IS NULL was evaluated to constant FALSE and optimized out from OR. In case of function the query was planned before substituting parameters, so the OR was still there and prevented index scan. Standard technique is to rewrite OR queries to UNION queries. I believe PostgreSQL optimizer does not do that automatically. So you could try instead: select * from id_bal_tbl where $1 is null union all select * from id_bal_tbl where id = $1; (Note: in general you would need UNION without ALL, to keep the semantics of OR.) Tambet ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Function to either return one or all records
"Tambet Matiisen" <[EMAIL PROTECTED]> writes: > Standard technique is to rewrite OR queries to UNION queries. I believe > PostgreSQL optimizer does not do that automatically. So you could try instead: > select * from id_bal_tbl where $1 is null > union all > select * from id_bal_tbl where id = $1; ... but that won't do anything to solve the performance problem. Doesn't help for the second select to use an index, if the first one grovels over the whole table anyway ... regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] checking pgsql functions
Hi folks, Is it possible to check plpgsql functions before they're actually into the database, as it's done w/ sql functions ? Often I've got the problem that a function still contains some syntax errors (well, nobody's perfect), but my application is quite critical (a single crashing trigger may cost a lot of money). So I'd like to checkin only syntactically correct (also with correct references). Is it possible somehow ? cu -- - Enrico Weigelt== metux IT service phone: +49 36207 519931 www: http://www.metux.de/ fax: +49 36207 519932 email: [EMAIL PROTECTED] cellphone: +49 174 7066481 - -- DSL ab 0 Euro. -- statische IP -- UUCP -- Hosting -- Webshops -- - ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] checking pgsql functions
Enrico Weigelt <[EMAIL PROTECTED]> writes: > Is it possible to check plpgsql functions before they're actually > into the database, as it's done w/ sql functions ? 8.0 does a little bit of this, and 8.1 will do more... regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] checking pgsql functions
On Thu, Apr 21, 2005 at 04:35:37AM +0200, Enrico Weigelt wrote: > > Is it possible to check plpgsql functions before they're actually > into the database, as it's done w/ sql functions ? What version of PostgreSQL are you using? 8.0 and later have a validator function for PL/pgSQL, although its syntax checks are minimal and errors can still get through. I'm sure a patch that improves it would be welcome :-) > Often I've got the problem that a function still contains some > syntax errors (well, nobody's perfect), but my application is > quite critical (a single crashing trigger may cost a lot of money). > So I'd like to checkin only syntactically correct (also with > correct references). Since the application is critical, hopefully you're running regression tests in a non-production environment before modifying the production system. Is that not the case? Regardless of how much syntax checking the DBMS does, it would still be prudent to test as many code paths as possible for semantic correctness. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(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] Function to either return one or all records
Hmmm, you're right. Is there any way to do a explain select * from id_val_tbl where false while explain doesn't tell SEQ_SCAN? I have to admit that I didn't performance tested this. I just saw explain writing SEQ_SCAN. |-Original Message- |From: Tom Lane [mailto:[EMAIL PROTECTED] |Sent: Mittwoch, 20. April 2005 23:06 |To: Tambet Matiisen |Cc: KÖPFERL Robert; pgsql-sql@postgresql.org |Subject: Re: [SQL] Function to either return one or all records | | |"Tambet Matiisen" <[EMAIL PROTECTED]> writes: |> Standard technique is to rewrite OR queries to UNION |queries. I believe PostgreSQL optimizer does not do that |automatically. So you could try instead: | |> select * from id_bal_tbl where $1 is null |> union all |> select * from id_bal_tbl where id = $1; | |... but that won't do anything to solve the performance problem. |Doesn't help for the second select to use an index, if the first |one grovels over the whole table anyway ... | | regards, tom lane | ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
