[SQL] Concerns about the OID

[Josh Berkus]

[SQL] trigger question

[Carolyn Lu Wong]

I have created a trigger when updating/inserting into a table, the row
is inserted into a log table. I get the following error when i tried to
update the table:

ERROR:  Unable to locate type oid 0 in catalog

what does this mean?

Re: [SQL] Something I'd like to try...

[Bruce Momjian]

Can someone comment on this?

> Hi,
> 
> I just noticed that postgres doesn't totally support
> column aliases on UPDATE statements, for example
>  
>  UPDATE EMPLOYEES SET
>  OFFICE_PHONE = UU.OFFICE_PHONE,
>  MOBILE_PHONE = UU.MOBILE_PHONE,
>  OFFICE_CD = UU.OFFICE_CD,
>  ABOUT_ME = UU.ABOUT_ME
>  FROM
>  UNCONFIRMED_UPDATES UU
>  WHERE
>  EMPLOYEES.EMPLOYEE_ID = UU.EMPLOYEE_ID AND
>  UU.UPDATE_ID = 'HJhjaJ023J19KJAqp'
> 
> It is not currently possible to alias EMPLOYEES
> so that the test can become
> 
>  E.EMPLOYEE_ID = UU.EMPLOYEE_ID
> 
> Do the guru's think that this would be hard to add?  Also,
> is it desirable?
> --
> 
> Niall Smart
> 
> email:  [EMAIL PROTECTED]
> phone:  (087) 8052390
> 


-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 853-3000
  +  If your life is a hard drive, |  830 Blythe Avenue
  +  Christ can be your backup.|  Drexel Hill, Pennsylvania 19026

Re: [SQL] Something I'd like to try...

[Cristóvão Dalla Costa]

It seems to me that aliases on updates are something totally superflous.
Since updates always work on one table at a time, why bother especifying the
table each field comes from?

> Can someone comment on this?
>
> > Hi,
> >
> > I just noticed that postgres doesn't totally support
> > column aliases on UPDATE statements, for example
> >
> >  UPDATE EMPLOYEES SET
> >  OFFICE_PHONE = UU.OFFICE_PHONE,
> >  MOBILE_PHONE = UU.MOBILE_PHONE,
> >  OFFICE_CD = UU.OFFICE_CD,
> >  ABOUT_ME = UU.ABOUT_ME
> >  FROM
> >  UNCONFIRMED_UPDATES UU
> >  WHERE
> >  EMPLOYEES.EMPLOYEE_ID = UU.EMPLOYEE_ID AND
> >  UU.UPDATE_ID = 'HJhjaJ023J19KJAqp'
> >
> > It is not currently possible to alias EMPLOYEES
> > so that the test can become
> >
> >  E.EMPLOYEE_ID = UU.EMPLOYEE_ID
> >
> > Do the guru's think that this would be hard to add?  Also,
> > is it desirable?
> > --

[SQL] trigger examples

[Carolyn Lu Wong]

I need to write a trigger to create a new record in log table if it's a
new record or if column A and B changes. Can someone give me an example
how to do this.

Are there any sites that gives trigger examples??

Thanks.

Re: [SQL] trigger examples

[drfrog]

Carolyn Lu Wong wrote:
> 
> I need to write a trigger to create a new record in log table if it's a
> new record or if column A and B changes. Can someone give me an example
> how to do this.
> 
> Are there any sites that gives trigger examples??
> 
> Thanks.

the fulltextindex in the contrib dir gives an example

[SQL] DB backup and connection questions

[Carolyn Lu Wong]

Can someone help me with the following?

1) Is there a command I can check which users are currently connecting
to a particular database? Is it possible to disconnect certain users who
are currently using the database? Can it also apply to connections to
the database via ODBC?

2) When doing database backup (pg_dump), is it necessary to 'kick' all
users connecting to the db?

Thanks in advance for any helps.

Re: [SQL] Something I'd like to try...

[Tom Lane]

Bruce Momjian <[EMAIL PROTECTED]> writes:
> Can someone comment on this?
>> 
>> I just noticed that postgres doesn't totally support
>> column aliases on UPDATE statements, for example

The SQL92 spec very clearly does not allow an alias on the target table:

 13.10  

  ::=
  UPDATE 
SET 
[ WHERE  ]

While I'm willing to consider variations from the spec that add
significant functionality, this proposed addition adds no functionality
worth noticing.  It'd just be another way to trip yourself up when
moving across DBMSes.

regards, tom lane

Re: [SQL] trigger examples

[Carolyn Lu Wong]

[EMAIL PROTECTED] wrote:
> 
> Carolyn Lu Wong wrote:
> >
> > I need to write a trigger to create a new record in log table if it's a
> > new record or if column A and B changes. Can someone give me an example
> > how to do this.
> >
> > Are there any sites that gives trigger examples??
> >
> > Thanks.
> 
> the fulltextindex in the contrib dir gives an example

The example uses C. Is it possible to create the trigger with plpsql
function?