[SQL]
Hi,
I'm writing a script, which has to retrieve a next value from a sequnce. I use
mod_perl, DBI and DBD::Pg version 0.98.
I notice that when I execute "select nextval('sequence_name'::text) AS
sname;", via DBI, the value of sequence is updated, but the mod_perl returns
"DBD::Pg::st execute failed: ERROR: sequence_name.nextval: you don't have
permissions to set sequence"
The above command is executed sucssesfuly in psql and returns the desire
result.
Does anybody has an idea what is the problem ?
Vladimir
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
Re: [SQL]
hi,
Grant all on sequence_name to theuser ;
regards
omid
>From: "Vladimir Terziev" <[EMAIL PROTECTED]>
>To: [EMAIL PROTECTED]
>Subject: [SQL]
>Date: Wed, 29 Aug 2001 10:40:00 +0300
>
>Hi,
>
>I'm writing a script, which has to retrieve a next value from a sequnce. I
>use
>mod_perl, DBI and DBD::Pg version 0.98.
>
>I notice that when I execute "select nextval('sequence_name'::text) AS
>sname;", via DBI, the value of sequence is updated, but the mod_perl
>returns
>"DBD::Pg::st execute failed: ERROR: sequence_name.nextval: you don't have
>permissions to set sequence"
>
>The above command is executed sucssesfuly in psql and returns the desire
>result.
>
>Does anybody has an idea what is the problem ?
>
> Vladimir
>
>
>
>---(end of broadcast)---
>TIP 6: Have you searched our list archives?
>
>http://www.postgresql.org/search.mpl
_
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
Re: [SQL] getting the oid for a new tuple in a BEFORE trigger
Mark, The responses to your problem are gonna be kinda slow, as 2/3 of the core team, and many of the users, are at the Expo right now (and if anyone on the list is in the SF Bay Area, join us! BOF session tonight!) > we need to control database changes within BEFORE triggers. > There is no problem with triggers called by update, but there is > a problem with triggers called by insert. What problem? > We strongly need to know the oid of a newly inserted tuple. In this > case, we > use tg_newtuple of the TriggerData structure passed to thetrigger > function, > and its t_data -> t_oid will have the value '0'. > > Using BEFORE and AFTER triggers would make our lives much harder. Once again, why? > Is there any way (even hack) to get the oid the newly inserted tuple > will > receive? This specific answer will have to come from someone else. I could suggest a couple of workarounds, if you gave a fuller description of exactly what you're trying to accomplish. -Josh Berkus P.S. Please do not cross-post to more than 2 lists at a time. The Postgres lists have been kept to a managable volume to date; let's keep it that way. __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] changes to table creation syntax in 7.1.2?
In postgres 7.0.x I had some working code that lookes something like: CREATE TABLE category ( uid int4 PRIMARY KEY, description text NOT NULL, parent int4 NULL REFERENCES category(uid) ) After upgrading to postgres 7.1.2 however this syntax is not accepted anymore. I receive an error that says it can not create the reference because the table category does not exist. How do I setup this type of reference structure in 7.1.2? Did the syntax change? I am running under Linux. -- Jayson Callaway ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] changes to table creation syntax in 7.1.2?
On Wed, 22 Aug 2001, Jayson Callaway wrote: > In postgres 7.0.x I had some working code that lookes something like: > > CREATE TABLE category > ( > uid int4 PRIMARY KEY, > description text NOT NULL, > parent int4 NULL REFERENCES category(uid) > ) > > After upgrading to postgres 7.1.2 however this syntax is not accepted > anymore. I receive an error that says it can not create the reference > because the table category does not exist. > > How do I setup this type of reference structure in 7.1.2? Did the syntax > change? Hmm, that's odd, that should have still worked (and does work on reasonably current sources). I'd have thought we'd have seen reports before now if this syntax got broken, but maybe not. Only other thing I could think of would be if the table name was a double quoted name of mixed case and not such in the references constraint. As an interim solution you can use alter table to add the references constraint after the table creation. ---(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] getting the oid for a new tuple in a BEFORE trigger
hi, Idon't know the best way but how about a quick insert in a temp table and adding 1 to the inserted oid column each time the trigger will run.! regards Omid >From: Markus Wagner <[EMAIL PROTECTED]> >To: [EMAIL PROTECTED], [EMAIL PROTECTED], >[EMAIL PROTECTED] >Subject: [SQL] getting the oid for a new tuple in a BEFORE trigger >Date: Wed, 29 Aug 2001 11:15:08 +0200 > >Hi, > >we need to control database changes within BEFORE triggers. >There is no problem with triggers called by update, but there is >a problem with triggers called by insert. > >We strongly need to know the oid of a newly inserted tuple. In this case, >we >use tg_newtuple of the TriggerData structure passed to thetrigger function, >and its t_data -> t_oid will have the value '0'. > >Using BEFORE and AFTER triggers would make our lives much harder. > >Is there any way (even hack) to get the oid the newly inserted tuple will >receive? > >Thank you very much, > >Markus > > >---(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 _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] getting the oid for a new tuple in a BEFORE trigger
Hi, we need to control database changes within BEFORE triggers. There is no problem with triggers called by update, but there is a problem with triggers called by insert. We strongly need to know the oid of a newly inserted tuple. In this case, we use tg_newtuple of the TriggerData structure passed to thetrigger function, and its t_data -> t_oid will have the value '0'. Using BEFORE and AFTER triggers would make our lives much harder. Is there any way (even hack) to get the oid the newly inserted tuple will receive? Thank you very much, Markus ---(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] changes to table creation syntax in 7.1.2?
It looks like it works here :(. Do you have an error message? processdata=# select version(); version --- PostgreSQL 7.1.2 on i686-pc-linux-gnu, compiled by GCC 2.95.4 (1 row) processdata=# CREATE TABLE category processdata-# ( processdata(# uid int4 PRIMARY KEY, processdata(# description text NOT NULL, processdata(# parent int4 NULL REFERENCES category(uid) processdata(# ) processdata-# processdata-# ; NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'category_pkey' for table 'category' NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) CREATE --- Jayson Callaway <[EMAIL PROTECTED]> wrote: > In postgres 7.0.x I had some working code that > lookes something like: > > CREATE TABLE category > ( > uid int4 PRIMARY KEY, > description text NOT NULL, > parent int4 NULL REFERENCES category(uid) > ) > > After upgrading to postgres 7.1.2 however this > syntax is not accepted > anymore. I receive an error that says it can not > create the reference > because the table category does not exist. > > How do I setup this type of reference structure in > 7.1.2? Did the syntax > change? > > I am running under Linux. > -- > Jayson Callaway > > > > ---(end of > broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] __ Do You Yahoo!? Get email alerts & NEW webcam video instant messaging with Yahoo! Messenger http://im.yahoo.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] GRANT ALL ON TO GROUP failure
Can anyone suggest, what is wrong with the following sql file? SOmehow the semicolon causes error? Jari class=# \i pg-def-group-grant.sql Granting ALL to ROOT CHANGE Granting ALL to USER psql:pg-def-group-grant.sql:48: ERROR: parser: parse error at or near "user" Granting SELECT to READER psql:pg-def-group-grant.sql:69: ERROR: non-existent group "reader" 01:-- Postgres create table rights 02:-- $Id: pg-def-group-grant.sql,v 1.1 2001/08/23 19:26:16 jaalto Exp $ 03:-- 04:-- 05:-- GRANT allows the creator of an object to give specific permissions to 06:-- all users (PUBLIC) or to a certain user or group. Users other than the 07:-- creator don't have any access permission unless the creator GRANTs 08:-- permissions, after the object is created. 09: 10:\echo Granting ALL to ROOT 11: 12:GRANT ALL ON 13: bonus 14: , custid 15: , customer 16: , dept 17: , dual 18: , dummy 19: , emp 20: , item 21: , ordid 22: , ordx 23: , price 24: , prodid 25: , product 26: , sales 27: , salgrade 28: TO GROUP root; 29: 30:\echo Granting ALL to USER 31: 32:GRANT ALL ON 33: bonus 34: , custid 35: , customer 36: , dept 37: , dual 38: , dummy 39: , emp 40: , item 41: , ordid 42: , ordx 43: , price 44: , prodid 45: , product 46: , sales 47: , salgrade 48: TO GROUP user; 49: 50: 51:\echo Granting SELECT to READER 52: 53:GRANT SELECT ON 54: bonus 55: , custid 56: , customer 57: , dept 58: , dual 59: , dummy 60: , emp 61: , item 62: , ordid 63: , ordx 64: , price 65: , prodid 66: , product 67: , sales 68: , salgrade 69: TO GROUP reader; 70: 71:-- End of file -- http://tiny-tools.sourceforge.net/ Swatch @time http://www.ryanthiessen.com/swatch/resources.htm Convert @time http://www.mir.com.my/iTime/itime.htm ---(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] GRANT ALL ON TO GROUP failure
Jari Aalto wrote: > >Can anyone suggest, what is wrong with the following >sql file? SOmehow the semicolon causes error? ... >Granting ALL to USER >psql:pg-def-group-grant.sql:48: ERROR: parser: parse error at or near "user >" ... >32:GRANT ALL ON >33: bonus >34: , custid >35: , customer >36: , dept >37: , dual >38: , dummy >39: , emp >40: , item >41: , ordid >42: , ordx >43: , price >44: , prodid >45: , product >46: , sales >47: , salgrade >48: TO GROUP user; user is a reserved word; it should not have been accepted as a group name unless double-quoted, in which case you need to double-quote it every time you mention it. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "Preach the word; be instant in season, out of season; reprove, rebuke, exhort with all longsuffering and doctrine." II Timothy 4:2 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] GRANT ALL ON TO GROUP failure
Jari Aalto writes: > class=# \i pg-def-group-grant.sql > Granting ALL to ROOT > CHANGE > Granting ALL to USER > psql:pg-def-group-grant.sql:48: ERROR: parser: parse error at or near "user" You need to double quote "user". It's a reserved word. > Granting SELECT to READER > psql:pg-def-group-grant.sql:69: ERROR: non-existent group "reader" Did you create a group named "reader"? -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(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] Latin-2 sort order
Hello, since a few hours I try to set-up Latin-2 sort order in PostgreSQL 7.1.3. I have re-compiled it with MB and Latin2, I have re-initialized database, I have restored dumps, which contained "ENCODING='LATIN2'" in it ... and it's still nothing. Could anyone send me a step-by-step instruction, or at least some URL? Please, I know it's RTFM-type-question, but the time is against me. I'm usint PGSQL on FreeBSD 4.3-STABLE. -- [ Michal Pasternak [EMAIL PROTECTED] +4860657 ] [ sklepy internetowe, bazy danych, programy na zamówienie ] [ . .. ..- .- . .. http://lublin.t1.pl . .-. .--.. . . .- ] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Latin-2 sort order
> since a few hours I try to set-up Latin-2 sort order in PostgreSQL 7.1.3. I > have re-compiled it with MB and Latin2, I have re-initialized database, I > have restored dumps, which contained "ENCODING='LATIN2'" in it ... and it's > still nothing. You need to enable locale support. MB support does nothing with the sort order. -- Tatsuo Ishii ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
