[SQL] triggers and plpgsql question
Hi I want to do a generic function that I can add to triggers to add every inserts, updates and deletes from many differents tables into a common format in another table. the idea is : create function do_it_all () returns opaque ' begin IF TG_OP = ''INSERT'' THEN cycle through all of NEW (not knowing what it contains) and do things with them ELSIF TG_OP = ''UPDATE'' THEN cycle through all of NEW (not knowing what it contains) and do things with them ELSIF TG_OP = ''DELETE'' THEN say that NEW.id_||TG_RELNAME has been deleted END IF; END; The thing I need, is to be able to know what does NEW contains, and I have not found out any mean to do so. If it's not possible to do so, I'll write a function per table, but for the beauty of all this, I would have liked to do it the way above. -- Mathieu Arnold ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] decode('hallo',???)
hallo somebody,
what do the functions "encode" and "decode" expect as arguments ?
I was puzzled when I got the following:
pgdocsample=# \encoding
SQL_ASCII
pgdocsample=# select decode('hallo','SQL_ASCII');
ERROR: No such encoding as 'SQL_ASCII'
What have I understood wrong ?
Fritz
---(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] decode('hallo',???)
Fritz Lehmann-Grube wrote:
>
> hallo somebody,
>
> what do the functions "encode" and "decode" expect as arguments ?
test=> \df decode
List of functions
Result data type | Schema | Name | Argument data types
--+++-
bytea| pg_catalog | decode | text, text
(1 row)
> I was puzzled when I got the following:
>
> pgdocsample=# \encoding
> SQL_ASCII
> pgdocsample=# select decode('hallo','SQL_ASCII');
> ERROR: No such encoding as 'SQL_ASCII'
>
> What have I understood wrong ?
Decode is working. The message is complaining about the encoding you
have for the session.
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] LIMIT 1 FOR UPDATE or FOR UPDATE LIMIT 1?
OK, no one has commented on this, so I guess I am going to have to guess the group's preference. My guess, seeing as very few probably use LIMIT and FOR UPDATE together, is to swap them and document it in the release notes. Was I correct in my guess? --- Bruce Momjian wrote: > Magnus Enbom wrote: > > On Mon, Aug 26, 2002 at 02:42:26PM -0400, Jan Wieck wrote: > > > Bruce Momjian wrote: > > > > > > > > I found this email from April. It properly points out that our > > > > LIMIT/FOR UPDATE ordering doesn't match MySQL's, and MySQL's looks more > > > > correct, specifically that the FOR UPDATE is after the LIMIT. Our > > > > grammar is: > > > > > > How do you define "correct" for "non-standard" features? And why don't > > > you ask Monty first to change to our "de-facto-standard"? ;-) > > > > Already done that. ;-) > > He said he would look into it(having MySQL accept both behaviors), but if > > it would require a big change of their grammar(for a value of big), he'd rather > > not. He also pointed out(as Bruce and Tom have done) that our(PG) way is > > kind of backwards. > > If you look at Oracle, you can see that they also have it last: > > > > select :== subquery -> for_update_clause ; > > > > OTOH, Oracle doesn't have LIMIT, but that's another story... > > > > Yep, we clearly have it backwards. Now, how to address it: > > 1) leave it unchanged > 2) allow only new ordering > 3) allow both orderings for one release > 4) allow both ordering forever > > > -- > Bruce Momjian| http://candle.pha.pa.us > [EMAIL PROTECTED] | (610) 359-1001 > + If your life is a hard drive, | 13 Roberts Road > + Christ can be your backup.| Newtown Square, Pennsylvania 19073 > > ---(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 > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] IDENT authentication problem
This is what I did: 1: I reinstalled postgresql RPMs from scratch (I removed all the logs, data files, backup files) 2: su root 3: su postgres 4: psql template1 And here I got the error message: psql: FATAL 1: IDENT authentication failed for user "foobar" User "foobar" was an old user I created for the database *before* I wiped out everything and reinstalled the RPMs. It is already gone from my /etc/password. (since I was using IDENT) What could have gone wrong? I must have left the trace of user foobar somewhere in my system but I couldn't find it. Thanks -- Wei Weng Network Software Engineer KenCast Inc. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] LIMIT 1 FOR UPDATE or FOR UPDATE LIMIT 1?
Bruce Momjian <[EMAIL PROTECTED]> writes: > My guess, seeing as very few probably use LIMIT and FOR UPDATE together, > is to swap them and document it in the release notes. That will surely piss someone off. Can't you try a little harder to support either order? regards, tom lane ---(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] LIMIT 1 FOR UPDATE or FOR UPDATE LIMIT 1?
On Tue, 2002-08-27 at 17:07, Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > My guess, seeing as very few probably use LIMIT and FOR UPDATE together, > > is to swap them and document it in the release notes. > > That will surely piss someone off. Can't you try a little harder to > support either order? If you change this you break me. I do this **A LOT** in the IP address allocation system I wrote. PLEASE DO NOT BREAK EXISTING APPS WITHOUT AT LEAST ONE RELEASE CYCLE'S WARNING, and preferably NOT AT ALL. -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] IDENT authentication problem
Wei Weng <[EMAIL PROTECTED]> writes: > What could have gone wrong? I must have left the trace of user foobar > somewhere in my system but I couldn't find it. PGUSER environment variable? regards, tom lane ---(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] triggers and plpgsql question
Mathieu, > The thing I need, is to be able to know what does NEW contains, and I have > not found out any mean to do so. If it's not possible to do so, I'll write > a function per table, but for the beauty of all this, I would have liked to > do it the way above. You can't do this in PL/pgSQL. See the online documentation on writing triggers in C; that is the only way to get what you want. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Problems with version 7.1, could they be fixed in 7.2?
Yes we did. By the way, how often is it recomended? Ligia "mark carew" <[EMAIL PROTECTED]> wrote in message akgr6p$vp2$[EMAIL PROTECTED]">news:akgr6p$vp2$[EMAIL PROTECTED]... > Hi Ligia, > > Are you running VACUUM ANALYSE or is it VACUUM ANALYZE (can never > remember, though reasonably sure that its the former). > > Regards Mark Carew > Brisbane Australia > > ---(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] LIMIT 1 FOR UPDATE or FOR UPDATE LIMIT 1?
Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > My guess, seeing as very few probably use LIMIT and FOR UPDATE together, > > is to swap them and document it in the release notes. > > That will surely piss someone off. Can't you try a little harder to > support either order? Sure. I just needed someone to say they want it before doing the work. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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] LIMIT 1 FOR UPDATE or FOR UPDATE LIMIT 1?
> OK, no one has commented on this, so I guess I am going to have to guess > the group's preference. > > My guess, seeing as very few probably use LIMIT and FOR UPDATE together, > is to swap them and document it in the release notes. Was I correct in > my guess? I'm sure very few people do it - but are you sure you can't just allow both syntaxes? Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Calculation Error on Epoch?
Folks,
I'm having a problem with:
SELECT date_part('epoch','2002-08-28'::TIMESTAMP)
Which is consistently returning an epoch timestamp that evaluates to
8.27.2002. Is this a known issue? A cross-platform problem?
Suggestions?
-Josh Berkus
---(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] Calculation Error on Epoch?
What if you try this:
SELECT date_part('epoch','2002-08-28'::TIMESTAMP WITH TIME ZONE)
It's probably 8 hours time different to GMT or somethign like that...
Chris
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]]On Behalf Of Josh Berkus
> Sent: Wednesday, 28 August 2002 11:31 AM
> To: [EMAIL PROTECTED]
> Subject: [SQL] Calculation Error on Epoch?
>
>
> Folks,
>
> I'm having a problem with:
>
> SELECT date_part('epoch','2002-08-28'::TIMESTAMP)
>
> Which is consistently returning an epoch timestamp that evaluates to
> 8.27.2002. Is this a known issue? A cross-platform problem?
> Suggestions?
>
> -Josh Berkus
>
> ---(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 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Calculation Error on Epoch?
"Josh Berkus" <[EMAIL PROTECTED]> writes:
> I'm having a problem with:
> SELECT date_part('epoch','2002-08-28'::TIMESTAMP)
> Which is consistently returning an epoch timestamp that evaluates to
> 8.27.2002. Is this a known issue? A cross-platform problem?
In 7.2 I get:
regression=# SELECT date_part('epoch','2002-08-28'::TIMESTAMP);
date_part
1030507200
(1 row)
which seems to be correct given my timezone:
$ prdate 1030507200
time 1030507200 = Wed Aug 28 2002, 00:00:00 EDT
(prdate is a homegrown utility that just does strftime(localtime(...)))
It does seem to be broken in CVS tip, as I've complained to Thomas
at least once:
regression=# SELECT date_part('epoch','2002-08-28'::TIMESTAMP);
date_part
---
1030.4928 -- wrong
(1 row)
regression=# SELECT date_part('epoch','2002-08-28'::TIMESTAMPTZ);
date_part
1030507200 -- right
(1 row)
What context are you testing in, and what do you get exactly?
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] triggers and plpgsql question
--On mardi 27 août 2002 15:38 -0700 Josh Berkus <[EMAIL PROTECTED]> wrote: > > Mathieu, > >> The thing I need, is to be able to know what does NEW contains, and I >> have not found out any mean to do so. If it's not possible to do so, >> I'll write a function per table, but for the beauty of all this, I would >> have liked to do it the way above. > > You can't do this in PL/pgSQL. See the online documentation on writing > triggers in C; that is the only way to get what you want. So, if I want to avoid C, I'll have to write a function per table. I'll have a look at SPI (as I believe after a short readout of the doc, I'll need it). -- Mathieu Arnold ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
