Обсуждение: SQL Script

On Wed, 30 Jan 2002, lonh SENG wrote:

> Hi all,
>
>     I have one table containing data as "source.txt" attached file. I
> want to create a script to clean the data and get the result as
> "result.txt" attached file.
>     inbtime and inbtime1 is datetime data type.
>     Anyone has an idea?
>     Any help will be appreciated.
>     thanks in advance
>
>     Sorry. It seems to be out of the topic.

Probably belongs on pgsql-sql, but... :)

This is not at all optimized, but maybe something of the form:

select t1.card, t1.inbtime, coalesce((select min(inbtime) from t t2 where
t2.inbtime>t1.inbtime and t2.card=t1.card), t1.inbtime) from t t1;

Thank all of you very much. It works now.
----- Original Message -----
From: "Stephan Szabo" <sszabo@megazone23.bigpanda.com>
To: "lonh SENG" <slonh@camgsm.com.kh>
Cc: <pgsql-admin@postgresql.org>
Sent: Wednesday, January 30, 2002 9:14 AM
Subject: Re: [ADMIN] SQL Script


>
> On Wed, 30 Jan 2002, lonh SENG wrote:
>
> > Hi all,
> >
> >     I have one table containing data as "source.txt" attached file. I
> > want to create a script to clean the data and get the result as
> > "result.txt" attached file.
> >     inbtime and inbtime1 is datetime data type.
> >     Anyone has an idea?
> >     Any help will be appreciated.
> >     thanks in advance
> >
> >     Sorry. It seems to be out of the topic.
>
> Probably belongs on pgsql-sql, but... :)
>
> This is not at all optimized, but maybe something of the form:
>
> select t1.card, t1.inbtime, coalesce((select min(inbtime) from t t2 where
> t2.inbtime>t1.inbtime and t2.card=t1.card), t1.inbtime) from t t1;
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
>

Greetings,

    I am sure I have seen this, but I have searched the docs, and
tried to search the archives, which was a definite exercise in futility.
How can I list the triggers on a specific table?  I tried the following:

=> select * from pg_trigger where tgargs like '%foo%' ;
ERROR:  Unable to identify an operator '~~' for types 'bytea' and 'unknown'
        You will have to retype this query using an explicit cast

The reason I ask is because I had a problem the other day.  I dropped a
table and the triggers didn't get deleted.  I couldn't do anything with
the other table the trigger was on because all of my constraints failed.
I had to go in and manually find and delete the triggers, which was not an
easy task.  Finding a couple needles out of 500+ triggers with no good
tools to help me was not fun.  HINT: I dumped pg_trigger with oid's to a
file, found the triggers using grep, and then deleted them by oid.

- brian

Wm. Brian McCane                    | Life is full of doors that won't open
Search http://recall.maxbaud.net/   | when you knock, equally spaced amid those
Usenet http://freenews.maxbaud.net/ | that open when you don't want them to.
Auction http://www.sellit-here.com/ | - Roger Zelazny "Blood of Amber"

Brian McCane <bmccane@mccons.net> writes:
> How can I list the triggers on a specific table?

select * from pg_trigger where tgrelid =
(select oid from pg_class where relname = 'foo');

This table is documented now in the 7.2 documentation; see
http://developer.postgresql.org/docs/postgres/catalog-pg-trigger.html

            regards, tom lane

Brian McCane wrote:
> How can I list the triggers on a specific table?

Just as I was typing this I noticed Tom already replied. Just incase you
want to know all triggers referencing the table in addition to those on it
(eg. foreign key constraints).

SELECT t.oid, t.*
FROM   pg_trigger t, pg_class c
WHERE  where c.oid in (t.tgrelid, t.tgconstrrelid)
  AND  c.relname = 'foo';

On Wed, 30 Jan 2002, Arguile wrote:

> Brian McCane wrote:
> > How can I list the triggers on a specific table?
>
> Just as I was typing this I noticed Tom already replied. Just incase you
> want to know all triggers referencing the table in addition to those on it
> (eg. foreign key constraints).
>
> SELECT t.oid, t.*
> FROM   pg_trigger t, pg_class c
> WHERE  where c.oid in (t.tgrelid, t.tgconstrrelid)
>   AND  c.relname = 'foo';
>

    Thanks Tom and Arguile that definitely helps.  Now for my more
pressing, but forgotten question.  Shouldn't PostgreSQL have automatically
dropped that trigger when I dropped the table?  I dropped and recreated a
small table with a Foreign Key, and each time I do it, I get more triggers
sitting out there that reference the name of the table. This is probably
not a problem if it doesn't reference the correct oid, but I am not sure.
The other problem I had was that I didn't name the constraints that I
created, so I had to delete them from 'pg_trigger' manually, then I had to
update the trigger count in 'pg_class' manually.  Would it have worked if
I had changed the name from '<unnamed>' to 'killme' using an update to
'pg_trigger', and then said, "DROP TRIGGER killme ON foo'?

- brian

Wm. Brian McCane                    | Life is full of doors that won't open
Search http://recall.maxbaud.net/   | when you knock, equally spaced amid those
Usenet http://freenews.maxbaud.net/ | that open when you don't want them to.
Auction http://www.sellit-here.com/ | - Roger Zelazny "Blood of Amber"

On Wed, 30 Jan 2002, Brian McCane wrote:

> On Wed, 30 Jan 2002, Arguile wrote:
>
> > Brian McCane wrote:
> > > How can I list the triggers on a specific table?
> >
> > Just as I was typing this I noticed Tom already replied. Just incase you
> > want to know all triggers referencing the table in addition to those on it
> > (eg. foreign key constraints).
> >
> > SELECT t.oid, t.*
> > FROM   pg_trigger t, pg_class c
> > WHERE  where c.oid in (t.tgrelid, t.tgconstrrelid)
> >   AND  c.relname = 'foo';
> >
>
>     Thanks Tom and Arguile that definitely helps.  Now for my more
> pressing, but forgotten question.  Shouldn't PostgreSQL have automatically
> dropped that trigger when I dropped the table?  I dropped and recreated a
> small table with a Foreign Key, and each time I do it, I get more triggers
> sitting out there that reference the name of the table. This is probably
> not a problem if it doesn't reference the correct oid, but I am not sure.

If you're using something below (I think) 7.1.3, pg_dump doesn't dump the
information on the other table in the foreign key so the info is lost
after a dump/restore sequence.  I was pretty sure this was fixed in 7.1.3
though.

> The other problem I had was that I didn't name the constraints that I
> created, so I had to delete them from 'pg_trigger' manually, then I had to
> update the trigger count in 'pg_class' manually.  Would it have worked if
> I had changed the name from '<unnamed>' to 'killme' using an update to
> 'pg_trigger', and then said, "DROP TRIGGER killme ON foo'?

IIRC you need to use the trigger name and not the constraint name for drop
trigger, so if you can find the rows, you should be able to do
DROP TRIGGER "RI_..." ON foo;  (the double quotes are required around the
trigger name.

Brian McCane <bmccane@mccons.net> writes:
>     Thanks Tom and Arguile that definitely helps.  Now for my more
> pressing, but forgotten question.  Shouldn't PostgreSQL have automatically
> dropped that trigger when I dropped the table?

It should, and it does.

IIRC, there's a bug in some versions of pg_dump that loses the
connection between a foreign-key trigger and the referenced table
(ie, the tgconstrrelid link) when you dump and reload.  If you can
exhibit an instance of misbehavior otherwise, I'd like to see it.

            regards, tom lane