Re: [SQL] Atomic query and update of sequence generators
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 > The goal is to get a sequence of 20 values that are > all +1 from each other. If you don't care about the rest of the values being +1 from each other, you could create the sequence with an INTERVAL of 20. Other than that, you would probably have to create your own "sequence" generator. Another alternative would be a function like this: get nextval as x set sequence to x+20 get nextval as y repeat if y != x+20 (i.e. someone else grabbed a value) - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200401150746 -BEGIN PGP SIGNATURE- iD8DBQFABovdvJuQZxSWSsgRAnxeAKCWK3tFCE3u8NfXG5LG3H0smDLyhACglrr7 08ke6k8B8MSKVipRb2aSWQg= =PSzM -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
Re: [SQL] Rule won't let me NOTIFY, no matter how hard I try
El Mar 13 Ene 2004 18:07, Jeff Boes escribió:
>JB: Here's the setup: I wanted to write a rule that would fire on an update
>JB: to one table, and do an update to another table, followed by a notify.
>JB: My first attempt wasn't acceptable to PG (7.3.4):
>JB:
>JB: create rule "my_rule" as
>JB: on update to table_A
>JB: where new.col_A != old.col_A
>JB: do
>JB:(update table_B ...;
>JB: notify "my_signal";
>JB:);
>JB:
>JB: ... because you can't have a "notify" statement in a rule that fires on
>JB: update (only select, update, and delete, I guess).
>JB:
hi,
you have to write to rules
first one (update):
create rule "my_rule" as
on update to table_A
where new.col_A != old.col_A
do
update table_B ...;
second one (notify):
create rule "my_rule2" as
on update to table_A
notify my_rule;
>JB: Second attempt was to "hide" the notify in a function:
>JB:
>JB: create function fn_notify(TEXT) returns VOID as '
>JB: execute ''notify " || $1 || "'';
>JB: ' language 'plpgsql';
The correct syntax...
create function fn_notify ( text ) RETURNS void as '
declare
v_signal alias for $1;
begin
execute '' notify "'' || v_signal || ''"'';
return;
end;
' language 'plpgsql';
...and re-write the first rule
create rule "my_rule" as
on update to table_A
where new.col_A != old.col_A
do
( update table_B ...;
SELECT fn_notify('my_signal'); );
CAVEAT: This rule always returns a tuple:
fn_notify
---
(1 row)
--
---
Luis Carlos Ferreira [EMAIL PROTECTED]
Centro de CómputosJunin 2957 - Santa Fe - Argentina
Sindicato de Luz y Fuerza Tel.: (54)(342) 4520-075
---
Estas loco? come vaca!!
---(end of broadcast)---
TIP 8: explain analyze is your friend
[SQL] Problem with plpgsql function
Hi All,
I've been bangin away on a 7.4.x based database server trying to get a
plpgsql function to work the way I'm expecting it to. I've used various
resourced on the net to come up with the function, and as far as I can
tell it's "in proper form", but I've got the feeling that I'm missing
something.
I've created a new data type called:
CREATE TYPE account_info AS (username text, password text);
With that I want to return multiple rows, based on the results of the
function, using the SETOF and rowtype declarations, such that the
function looks like:
CREATE OR REPLACE FUNCTION get_account_info(text) RETURNS SETOF
account_info AS '
DECLARE
acc account_info%rowtype;
domain_name ALIAS FOR $1;
company_id RECORD;
BEGIN
acc.username := NULL;
acc.password := NULL;
SELECT INTO company_id cs.id, to_char(cs.id, ''FM0999'') AS cid FROM
virtual_host vh
LEFT JOIN virtual_machine vm ON (vm.id = vh.vm_id)
LEFT JOIN company_summary cs ON (cs.id = vm.company_id)
WHERE vh.domain_name = domain_name;
FOR acc IN EXECUTE ''SELECT || company_id.cid || || c.id,
a.password FROM company_summary cs
LEFT JOIN contact c ON (c.company_id =
cs.id)
LEFT JOIN company_'' || company_id.cid ||
''.account a ON (a.contact_id = c.id)
WHERE cs.id = '' || company_id.id
LOOP
RETURN NEXT acc;
END LOOP;
RETURN;
END;
' LANGUAGE plpgsql;
According to the system, the function is created with out issue, and
there appear to not be any syntax errors being returned, however when I
execute the function in the query like this:
select get_account_info('test.com');
I get this error:
ERROR: set-valued function called in context that cannot accept a set
The backend logger results look like:
Jan 15 13:42:56 jupiter 5439[3164]: [128-1] LOG: query: select
get_account_info('test.com');
Jan 15 13:42:56 jupiter 5439[3164]: [129-1] LOG: query: SELECT NULL
Jan 15 13:42:56 jupiter 5439[3164]: [129-2] CONTEXT: PL/pgSQL function
"get_account_info" line 7 at assignment
Jan 15 13:42:56 jupiter 5439[3164]: [130-1] LOG: query: SELECT NULL
Jan 15 13:42:56 jupiter 5439[3164]: [130-2] CONTEXT: PL/pgSQL function
"get_account_info" line 8 at assignment
Jan 15 13:42:56 jupiter 5439[3164]: [131-1] LOG: query: SELECT cs.id,
to_char(cs.id, 'FM0999') AS cid FROM virtual_host vh LEFT JOIN
virtual_machine vm ON (vm.id =
Jan 15 13:42:56 jupiter 5439[3164]: [131-2] vh.vm_id) LEFT JOIN
company_summary cs ON (cs.id = vm.company_id) WHERE vh.domain_name = $1
Jan 15 13:42:56 jupiter 5439[3164]: [131-3] CONTEXT: PL/pgSQL function
"get_account_info" line 10 at select into variables
Jan 15 13:42:56 jupiter 5439[3164]: [132-1] LOG: query: SELECT 'SELECT
'' || company_id.cid || '' || c.id, a.password FROM company_summary cs
Jan 15 13:42:56 jupiter 5439[3164]:
[132-2] LEFT JOIN contact c ON
(c.company_id = cs.id)
Jan 15 13:42:56 jupiter 5439[3164]:
[132-3] LEFT JOIN company_' || $1 ||
'.account a ON (a.contact_id = c.id)
Jan 15 13:42:56 jupiter 5439[3164]:
[132-4] WHERE cs.id = ' || $2
Jan 15 13:42:56 jupiter 5439[3164]: [132-5] CONTEXT: PL/pgSQL function
"get_account_info" line 15 at for over execute statement
Jan 15 13:42:56 jupiter 5439[3164]: [133-1] LOG: query: SELECT ' ||
company_id.cid || ' || c.id, a.password FROM company_summary cs
Jan 15 13:42:56 jupiter 5439[3164]:
[133-2] LEFT JOIN contact c ON
(c.company_id = cs.id)
Jan 15 13:42:56 jupiter 5439[3164]:
[133-3] LEFT JOIN company_0011.account a
ON (a.contact_id = c.id)
Jan 15 13:42:56 jupiter 5439[3164]:
[133-4] WHERE cs.id = 11
Jan 15 13:42:56 jupiter 5439[3164]: [133-5] CONTEXT: PL/pgSQL function
"get_account_info" line 15 at for over execute statement
Jan 15 13:42:56 jupiter 5439[3164]: [134-1] ERROR: set-valued function
called in context that cannot accept a set
Jan 15 13:42:56 jupiter 5439[3164]: [134-2] CONTEXT: PL/pgSQL function
"get_account_info" line 20 at return next
Can anyone see anything that I missed? Or has any suggestions?
--
Chris Bowlby <[EMAIL PROTECTED]>
Hub.Org Networking Services
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [SQL] Problem with plpgsql function
Chris Bowlby wrote:
select get_account_info('test.com');
I get this error:
ERROR: set-valued function called in context that cannot accept a set
This is the "classic" SRF error -- you need to use an SRF like a
relation in the FROM clause, so do this instead:
select * FROM get_account_info('test.com');
HTH,
Joe
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Problem with plpgsql function
Ack, I knew it was something small, I was under the impression that I
had been using that, it just took someone to point it out to make me
look again :>... thanks..
On Thu, 2004-01-15 at 14:47, Joe Conway wrote:
> Chris Bowlby wrote:
> > select get_account_info('test.com');
> >
> > I get this error:
> >
> > ERROR: set-valued function called in context that cannot accept a set
>
> This is the "classic" SRF error -- you need to use an SRF like a
> relation in the FROM clause, so do this instead:
>
>select * FROM get_account_info('test.com');
>
> HTH,
>
> Joe
--
Chris Bowlby <[EMAIL PROTECTED]>
Hub.Org Networking Services
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[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
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 8: explain analyze is your friend
