Обсуждение: Basic Trigger

Hi all,

I did some Trigger to set default values for each row interted.
so, I'm using NEW.ID = NEXTVAL(''products_id_seq'');
but the column do not assum this value...
must I execute any other command before to set a value for NEW?

CREATE OR REPLACE FUNCTION "f_products"() RETURNS "opaque" AS '
BEGIN
   NEW.ID = NEXTVAL(''products_id_seq'');
   RETURN NEW;
END' LANGUAGE 'plpgsql'

CREATE TRIGGER "t_classes" AFTER INSERT ON "products" FOR EACH ROW EXECUTE
PROCEDURE f_products();


Roberto de Amorim  +55 48 346-2243
Software engineer at SmartBit Software
roberto@smartbit.inf.br

On 18.11 09:13, Roberto de Amorim wrote:
> I did some Trigger to set default values for each row interted.
> so, I'm using NEW.ID = NEXTVAL(''products_id_seq'');
> but the column do not assum this value...
> must I execute any other command before to set a value for NEW?

Why not use serial datatype in this case?
http://www.postgresql.org/idocs/index.php?datatype.html#DATATYPE-SERIAL

If you want to have a trigger inserting a default a value, why
not just add a DEFAULT clause to the table definition / use
alter table?

If you want to create the trigger by yourself, first read:
http://www.postgresql.org/idocs/index.php?triggers.html

- Einar Karttunen

On Mon, 18 Nov 2002, Roberto de Amorim wrote:

> Hi all,
>
> I did some Trigger to set default values for each row interted.
> so, I'm using NEW.ID = NEXTVAL(''products_id_seq'');
> but the column do not assum this value...
> must I execute any other command before to set a value for NEW?
>
> CREATE OR REPLACE FUNCTION "f_products"() RETURNS "opaque" AS '
> BEGIN
>    NEW.ID = NEXTVAL(''products_id_seq'');
>    RETURN NEW;
> END' LANGUAGE 'plpgsql'
>
> CREATE TRIGGER "t_classes" AFTER INSERT ON "products" FOR EACH ROW EXECUTE
> PROCEDURE f_products();

Apart from the fact that serial or a default is easier, if you want to
modify the row to be inserted (or the updated value of an update), you
want a BEFORE trigger, not an AFTER one.

Hi all,

is there anyone using pgSQL with ZEOS components for Delphi??

Which type of field are you using to save images like .BMP or .JPEG??

TIA

Roberto de Amorim

Hi! Daniel,

>
> you should use oid Fields for BLOBS. That works well.
>

I have the same question. Could you make it more specific? oid is an
unsigned 4 bytes integer. How do you save binary data from Delphi in
bytea column "using oid fields"?

Thanks you!

CN

Hi,

you should use oid Fields for BLOBS. That works well.

Daniel.

""Roberto de Amorim"" <roberto@smartbit.inf.br> schrieb im Newsbeitrag
news:001601c2d68e$4f2107a0$04c8a8c0@Desenvolvimento...
> Hi all,
>
> is there anyone using pgSQL with ZEOS components for Delphi??
>
> Which type of field are you using to save images like .BMP or .JPEG??
>
> TIA
>
> Roberto de Amorim
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

BLOBs are actually created using lo_create - you are then returned an OID
that can essentially be used as a file descriptor.  However, in my
programs, I've opted for bytea for the following reasons:

 * You can't query for BLOBs and know if there is a dangling reference

 * BLOBs use OID fields.  Running out of OIDs is a problem in PostgreSQL
because they are only four bytes wide.  This makes BLOBs useless for large
databases.

 * bytea fields are easier to manipulate

I think I had some other reaons, but I can't think of them right now.

I also just liked storing the binary object itself as an attribute rather
than a somewhat dangling entity.

Jon

On 18 Feb 2003, CN wrote:

> Hi! Daniel,
>
> >
> > you should use oid Fields for BLOBS. That works well.
> >
>
> I have the same question. Could you make it more specific? oid is an
> unsigned 4 bytes integer. How do you save binary data from Delphi in
> bytea column "using oid fields"?
>
> Thanks you!
>
> CN
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>

Are you getting to use BYTEA or OID fields?
Are you using ZEOS components?

 I had to change them sources of Zeos to it recognize the bytea field as
TBlobField ...



----- Original Message -----
From: "CN" <cnliou9@fastmail.fm>
To: <pgsql-general@postgresql.org>
Sent: Wednesday, February 19, 2003 2:55 AM
Subject: Re: [GENERAL] BLOB or BYTEA field


Hi! Daniel,

>
> you should use oid Fields for BLOBS. That works well.
>

I have the same question. Could you make it more specific? oid is an
unsigned 4 bytes integer. How do you save binary data from Delphi in
bytea column "using oid fields"?

Thanks you!

CN

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org