Re: [SQL] obtaining the query string inside a trigger
Gerardo Herzig wrote: Hi all. Im working on a "on each statement" update trigger, so NEW and OLD are NULL. Suppose a simple query like 'update mytable set id=id+500 where id < 50'...There is a way to obtaining the 'set id=..' and the where clause in some way? Afraid not. It might be possible to implement in the simple case, but what with views rewriting queries and triggers issuing their own secondary queries it's not always obvious what "the" query would be. What we really need is NEW and OLD as sets of affected rows in statement-queries. Unfortunately that's not implemented yet either - it's on the TODO I believe though. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Update PK Violation
> Franklin Haut wrote: > > Hi all, > > i have a problem with one update sentence sql. > > example to produce: > > create table temp (num integer primary key, name varchar(20)); > > insert into temp values (1, 'THE'); > insert into temp values (2, 'BOOK'); > insert into temp values (3, 'IS'); > insert into temp values (4, 'ON'); > insert into temp values (5, 'THE'); > insert into temp values (6, 'RED'); > insert into temp values (7, 'TABLE'); > Couldn't figure out how to do it in one sentence, still it can be done with a function: CREATE OR REPLACE FUNCTION insert_value(p_num integer, p_name varchar(20)) RETURNS VOID AS $$ declare v_num integer; BEGIN FOR v_num in SELECT num FROM temp WHERE num >= p_num ORDER BY num DESC LOOP UPDATE temp SET num = num + 1 WHERE num = v_num; END LOOP; INSERT INTO temp VALUES (p_num, p_name); END; $$ LANGUAGE 'plpgsql' VOLATILE; To run it: sistema=# select insert_value(4, 'NOT'); insert_value -- (1 row) sistema=# select * from temp order by num; num | name -+--- 1 | THE 2 | BOOK 3 | IS 4 | NOT 5 | ON 6 | THE 7 | RED 8 | TABLE (8 rows) Regards, Fernando. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Update PK Violation
Στις Tuesday 15 January 2008 23:03:49 ο/η Franklin Haut έγραψε: > Hi all, > > i have a problem with one update sentence sql. > A simple way i use: foodb=# update temp set num = num*1000 where num >= 5; foodb=# insert into temp values (5, 'NOT'); foodb=# update temp set num = 1 + num/1000 where num >= 6; -- Achilleas Mantzios ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] SQL dealing with subquery
Thanks. It throws off a few extra countries where there is only one treaty, but
those are
few enough that I can handle them manually.
I thought the solution was also going to give me insight into how to select
just the lowest
rate from each couple, (i.e. for each payor, who is the lowest rate payee) but
it looks like
I'll have find some time to think about that later (I've also got to think
about what to do in tie situations.).
Again, thanks.
Bryan
On Tuesday 15 January 2008 12:40:13 pm Rodrigo E. De León Plicet wrote:
> On Jan 15, 2008 1:04 PM, Bryan Emrys <[EMAIL PROTECTED]> wrote:
> > In other words, in the sample above, I only want to return:
> > 'Canada','Ireland',0
> > 'Canada','Netherlands',5
>
> Try (untested):
>
> SELECT t2.*
> FROM (SELECT payor
> FROM treaty_rates
> WHEREpayee IN ('Netherlands', 'Ireland')
> GROUP BY payor
> HAVING MIN (rate) != MAX (rate)) t1
>JOIN
>treaty_rates t2 ON t1.payor = t2.payor
> WHERE t2.payee IN ('Netherlands', 'Ireland');
>
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Update PK Violation
On Jan 16, 2008 8:30 AM, Achilleas Mantzios <[EMAIL PROTECTED]> wrote: > Στις Tuesday 15 January 2008 23:03:49 ο/η Franklin Haut έγραψε: > > Hi all, > > > > i have a problem with one update sentence sql. > > > > A simple way i use: > > foodb=# update temp set num = num*1000 where num >= 5; > foodb=# insert into temp values (5, 'NOT'); > foodb=# update temp set num = 1 + num/1000 where num >= 6; That's still open to possible collisions. Another method that avoids them is to use negative numbers. i.e. update temp set num = -1*num where num >=5; insert into temp values (5,'NOT';); update temp set num = (-1*num) + 1 where num < 0; Assuming you don't use negative numbers in your setup, works a charm. However, this kind of activity screams "bad design"... Not that I've never found myself right smack dab in the middle of such a thing ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Update PK Violation
Scott Marlowe wrote: On Jan 15, 2008 3:03 PM, Franklin Haut <[EMAIL PROTECTED]> wrote: Hi all, i have a problem with one update sentence sql. example to produce: create table temp (num integer primary key, name varchar(20)); insert into temp values (1, 'THE'); insert into temp values (2, 'BOOK'); insert into temp values (3, 'IS'); insert into temp values (4, 'ON'); insert into temp values (5, 'THE'); insert into temp values (6, 'RED'); insert into temp values (7, 'TABLE'); -- now i need insert new row at position 4, for this i need increase the field 'num' 4 to 5, 5 to 6, 6 to 7 and 7 to 8 update temp set num = num + 1 where num > 5; -- but this give an key violation error, because the postgresql try change row 4 to 5 and the 5 exist. --- the big question is... have one way to the command (update temp SNIP set num = num + 1 where num > 5;) works ? -- consideration, i can´t delete the primary key -- using PG 8.2 / Windows Normally, I'd say you're doing it wrong, as PKs aren't supposed to change all the time. You're using this as a uniquer sequencer number, not a real PK. However, there are a few different work-arounds you might be able to implement, depending on your needs. 1: Drop the unique index in a transaction, put it back before you're done. This method has some serious locking issues you might run into, but if you only have one or two processes accessing your data, and it all happens in a quick succession, it should be safe. Since, if something in your activity fails, the transaction rolls back and your original unique index is still there. begin; drop index abc_pk_dx; update table set id = id + 1 where id > 5; create index unique abc_pk_dx on table (id); commit; 2: Put gaps in your sequence. Since you're not likely to have billions of billions of words, you can put gaps in your id sequence. I.e. 0, 20, 40, 60, 80, 100, so on. Add a word in the middle just give it a number like 50. If you run out of space, then lock the table and spread it out again. Shouldn't be necessary very often, if ever. If you need unlimited space between each, then switch to numeric. 3: Use an id to numeric lookup table. I.e. have a table hanging off to the side that has the REAL sequence numbers, and don't ever change them in the original table, but have another column there (or in the side table) that connects them to each other. Hope one of those ideas helps. You solve my problem when you say " I'd say you're doing it wrong, as PKs aren't supposed to change all the time. " I changed my table and add a new colum what is part of the PK. another solution is that Achilleas Mantzios and you propoused using negative values. update temp set num = -1*num where num >=5; insert into temp values (5,'NOT';); update temp set num = (-1*num) + 1 where num < 0; thanks for all!!
Re: [SQL] SQL dealing with subquery
Following up my treaty rate thoughts, if I'm trying to get the lowest treaty
payee (and rate) from a specific list of payees for every possible payor
country, the following seems to work, but is it right? I'm specifically
wondering about the group by clauses. (Or if there is a better way.) [table
treaties has columns payor,payee,rate and in this sample, I'm just trying to
find which country payee between Ireland and the Netherlands has the lowest
rate from each individual payor country.]
select a.payor,a.payee,a.rate
from treaties a,
(select payor,min(rentr) from treaties
where payee in ('Ireland','Netherlands') group by payor
) b
where a.payor=b.payor
and a.rate = b.min
and payee in ('Ireland','Netherlands')
group by a.payor, a.payee, a.rate
[SQL] How to test/read a stored procedure that returns a boolean?
I wrote this function but I'm not sure how to test it in PG Admin III
Query.
CREATE LANGUAGE 'plpgsql' HANDLER plpgsql_call_handler
LANCOMPILER 'PL/pgSQL';
CREATE TABLE handles (
handleID_pk serial PRIMARY KEY UNIQUE NOT NULL,
userID_fk integer UNIQUE NOT NULL,
handle text UNIQUE NOT NULL);
CREATE TABLE disallowedHandles (
handle text UNIQUE NOT NULL);
create or replace function IsUsedHandle(h text) returns boolean as $$
declare
begin
select COUNT(*) as num_matches from handles where handles.handle = h;
return num_matches > 0;
end;
$$ LANGUAGE plpgsql;
INSERT INTO handles (handle, userid_fk) VALUES ('blah', 0);
select * from IsUsedHandle('k');
Instead of true or false, it says
ERROR: query has no destination for result data
SQL state: 42601
Hint: If you want to discard the results of a SELECT, use PERFORM instead.
Context: PL/pgSQL function "isusedhandle" line 3 at SQL statement
If I wanted to call this query and get the boolean result in C++,
using PQgetvalue, how would I get this?
Thanks!
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] How to test/read a stored procedure that returns a boolean?
Kevin Jenkins <[EMAIL PROTECTED]> writes: > create or replace function IsUsedHandle(h text) returns boolean as $$ > declare > begin > select COUNT(*) as num_matches from handles where handles.handle = h; > return num_matches > 0; > end; > $$ LANGUAGE plpgsql; I think you've confused AS with INTO. You forgot to declare num_matches as a local variable, too. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] How to test/read a stored procedure that returns a boolean?
Thanks Tom!
Also, how do I check if a language is already created so I don't load
it twice?
"ERROR: language "plpgsql" already exists
SQL state: 42710"
Here is the code fixed.
/*
CREATE LANGUAGE 'plpgsql' HANDLER plpgsql_call_handler
LANCOMPILER 'PL/pgSQL';
CREATE TABLE handles (
handleID_pk serial PRIMARY KEY UNIQUE NOT NULL,
userID_fk integer UNIQUE NOT NULL,
handle text UNIQUE NOT NULL);
CREATE TABLE disallowedHandles (
handle text UNIQUE NOT NULL);
*/
create or replace function IsUsedHandle(h text) returns boolean as $$
declare
num_matches integer;
begin
num_matches := COUNT(*) from handles where handles.handle = h;
return num_matches > 0;
end;
$$ LANGUAGE plpgsql;
-- INSERT INTO handles (handle, userid_fk) VALUES ('blah', 0);
select * from IsUsedHandle('blah');
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
