Re: quering NULL values

I there, is it possible to send a undefine number of queries (SELECTS) with
transactions, and if one of then doesn't return anything do rollback and do
not commit? if not, is there another tool that does that. I need to get the
result of the COMMIT. thanks in advance

_________________________________________________________________
Express yourself instantly with MSN Messenger! Download today it's FREE!
http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/

On 16:36 Thu 05 Jan , Luis Silva wrote:
> I there, is it possible to send a undefine number of queries (SELECTS) with
> transactions, and if one of then doesn't return anything do rollback and do
> not commit? if not, is there another tool that does that. I need to get the
> result of the COMMIT. thanks in advance

If you're only selecting, there would be nothing to rollback

Regards,
Andy

I there!! I'm using pl/pgsql in my application, but I'm having a problem
with the returning value. What my function does is quering the db and
according to the result of the different queries returns a error message or
some information from the db. I'm inserting the correct information in a
record variable, but I cant insert text in a variable like this,the error
message created by me. Is it possible, or is there another returning type
that I can use??

DECLARE
val_pub text;
asds Record;

BEGIN
RAISE NOTICE 'validate_user()';
val_pub:=$1;

EXECUTE 'SELECT count(*) FROM public_identif WHERE
identity='||quote_literal(val_pub) INTO asds;

IF asds.count=0 THEN
RETURN 'DIAMETER_ERROR_USER_UNKNOWN';
END IF;

EXECUTE 'SELECT name FROM public_identif WHERE
identity='||quote_literal(val_pub) INTO asds;

RETURN name; -----array of text!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

RETURN NULL;
END;

tks a lot in advance

I there I'm having a problem.

I'm doing this SQL query

select count(*) from public_identif INNER JOIN ifc ON
ifc.spid=public_identif.spid where (ifc.profilepartindicator='' OR
ifc.profilepartindicator='UNREGISTERED') AND
public_identif.identity='sip:joao(at)ptinovacao(dot)pt'

ifc.profilepartindicator='' can be a NULL value that is why i'm doing this,
but it's not working. I tried ifc.profilepartindicator=NULL but i get
nothing again. Can you help please?? Tks a lot

Luis Silva wrote:
> I there I'm having a problem.
>
> I'm doing this SQL query
>
> select count(*) from public_identif INNER JOIN ifc ON
> ifc.spid=public_identif.spid where (ifc.profilepartindicator='' OR
> ifc.profilepartindicator='UNREGISTERED') AND
> public_identif.identity='sip:joao(at)ptinovacao(dot)pt'
>
>
> ifc.profilepartindicator='' can be a NULL value that is why i'm doing
> this, but it's not working. I tried ifc.profilepartindicator=NULL but i
> get nothing again. Can you help please?? Tks a lot

change your query as follows:

select count(*) from public_identif INNER JOIN ifc ON
ifc.spid=public_identif.spid where (ifc.profilepartindicator='' OR
ifc.profilepartindicator IS NULL OR
ifc.profilepartindicator='UNREGISTERED') AND
public_identif.identity='sip:joao(at)ptinovacao(dot)pt'

Note the addition of "ifc.profilepartindicator IS NULL" within the
parentheses.

Charley

>From: Charley Tiggs <ctiggs(at)xpressdocs(dot)com>
>To: Luis Silva <lfs12(at)hotmail(dot)com>
>CC: pgsql-novice(at)postgresql(dot)org
>Subject: Re: [NOVICE] quering NULL values
>Date: Mon, 30 Jan 2006 09:47:10 -0600
>
>Luis Silva wrote:
>>I there I'm having a problem.
>>
>>I'm doing this SQL query
>>
>>select count(*) from public_identif INNER JOIN ifc ON
>>ifc.spid=public_identif.spid where (ifc.profilepartindicator='' OR
>>ifc.profilepartindicator='UNREGISTERED') AND
>>public_identif.identity='sip:joao(at)ptinovacao(dot)pt'
>>
>>
>>ifc.profilepartindicator='' can be a NULL value that is why i'm doing
>>this, but it's not working. I tried ifc.profilepartindicator=NULL but i
>>get nothing again. Can you help please?? Tks a lot
>
>
>change your query as follows:
>
>select count(*) from public_identif INNER JOIN ifc ON
>ifc.spid=public_identif.spid where (ifc.profilepartindicator='' OR
>ifc.profilepartindicator IS NULL OR
>ifc.profilepartindicator='UNREGISTERED') AND
>public_identif.identity='sip:joao(at)ptinovacao(dot)pt'
>
>Note the addition of "ifc.profilepartindicator IS NULL" within the
>parentheses.
>
>Charley
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: explain analyze is your friend

I there!! I'm trying to use regular expressions with postgresql. My
objective is to get from a long string the information that I need.
For example

"name='joe' , address='portugal' " and I need to get 'joe' and 'portugal'.
can I do it with select substring()? if I can,how? tks a lot

am 13.02.2006, um 10:53:48 +0000 mailte Luis Silva folgendes:
>
> I there!! I'm trying to use regular expressions with postgresql. My
> objective is to get from a long string the information that I need.
> For example
>
> "name='joe' , address='portugal' " and I need to get 'joe' and 'portugal'.
> can I do it with select substring()? if I can,how? tks a lot

test=# select * from foo;
string
---------------------------------
name='joe' , address='portugal'
(1 row)

Write a function:
- count the fields separeted by ',' and then for every field:

test=# select regexp_replace(split_part(string,',',1), '\\m.*=', '') from foo;
regexp_replace
----------------
'joe'
(1 row)

test=# select regexp_replace(split_part(string,',',2), '\\m.*=', '') from foo;
regexp_replace
----------------
'portugal'
(1 row)

You can with trim() remove spaces if you need.

HTH, Andreas
--
Andreas Kretschmer (Kontakt: siehe Header)
Heynitz: 035242/47215, D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
=== Schollglas Unternehmensgruppe ===

HI!!! that worked fine, but is it possible to get 'name' and 'address'
using the regexp_replace too? TKS A LOT again

>From: "A. Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com>
>To: pgsql-novice(at)postgresql(dot)org
>Subject: Re: [NOVICE] substring result
>Date: Mon, 13 Feb 2006 13:06:28 +0100
>
>am 13.02.2006, um 10:53:48 +0000 mailte Luis Silva folgendes:
> >
> > I there!! I'm trying to use regular expressions with postgresql. My
> > objective is to get from a long string the information that I need.
> > For example
> >
> > "name='joe' , address='portugal' " and I need to get 'joe' and
>'portugal'.
> > can I do it with select substring()? if I can,how? tks a lot
>
>test=# select * from foo;
> string
>---------------------------------
> name='joe' , address='portugal'
>(1 row)
>
>
>Write a function:
>- count the fields separeted by ',' and then for every field:
>
>
>test=# select regexp_replace(split_part(string,',',1), '\\m.*=', '') from
>foo;
> regexp_replace
>----------------
> 'joe'
>(1 row)
>
>test=# select regexp_replace(split_part(string,',',2), '\\m.*=', '') from
>foo;
> regexp_replace
>----------------
> 'portugal'
>(1 row)
>
>
>You can with trim() remove spaces if you need.
>
>
>
>HTH, Andreas
>--
>Andreas Kretschmer (Kontakt: siehe Header)
>Heynitz: 035242/47215, D1: 0160/7141639
>GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
> === Schollglas Unternehmensgruppe ===
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: explain analyze is your friend

Ok , I've used .....,'\\M.*','') tks again

>From: "Luis Silva" <lfs12(at)hotmail(dot)com>
>To: andreas(dot)kretschmer(at)schollglas(dot)com, pgsql-novice(at)postgresql(dot)org
>Subject: Re: [NOVICE] substring result
>Date: Mon, 13 Feb 2006 15:36:06 +0000
>
>HI!!! that worked fine, but is it possible to get 'name' and 'address'
>using the regexp_replace too? TKS A LOT again
>
>>From: "A. Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com>
>>To: pgsql-novice(at)postgresql(dot)org
>>Subject: Re: [NOVICE] substring result
>>Date: Mon, 13 Feb 2006 13:06:28 +0100
>>
>>am 13.02.2006, um 10:53:48 +0000 mailte Luis Silva folgendes:
>> >
>> > I there!! I'm trying to use regular expressions with postgresql. My
>> > objective is to get from a long string the information that I need.
>> > For example
>> >
>> > "name='joe' , address='portugal' " and I need to get 'joe' and
>>'portugal'.
>> > can I do it with select substring()? if I can,how? tks a lot
>>
>>test=# select * from foo;
>> string
>>---------------------------------
>> name='joe' , address='portugal'
>>(1 row)
>>
>>
>>Write a function:
>>- count the fields separeted by ',' and then for every field:
>>
>>
>>test=# select regexp_replace(split_part(string,',',1), '\\m.*=', '') from
>>foo;
>> regexp_replace
>>----------------
>> 'joe'
>>(1 row)
>>
>>test=# select regexp_replace(split_part(string,',',2), '\\m.*=', '') from
>>foo;
>> regexp_replace
>>----------------
>> 'portugal'
>>(1 row)
>>
>>
>>You can with trim() remove spaces if you need.
>>
>>
>>
>>HTH, Andreas
>>--
>>Andreas Kretschmer (Kontakt: siehe Header)
>>Heynitz: 035242/47215, D1: 0160/7141639
>>GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
>> === Schollglas Unternehmensgruppe ===
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 6: explain analyze is your friend
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly

I there!!! Can you help with an urgent question. I'm doing a pl/pgsql
function and need to return a record value or a text value. Can I pass the
text to record? the return type "unknown" works for this?? does it has a
good performance? tks a lot

sorry again. another question. When I return a Record value I only get the
first row of the result. What might be the problem? tks again

am 15.02.2006, um 12:03:29 +0000 mailte Luis Silva folgendes:
>
> sorry again. another question. When I return a Record value I only get the
> first row of the result. What might be the problem? tks again

Please:

If you start a new thread (question), then start a new thread and don't
change the subject from a old mail. Many people have mail-clients with
the possibility to show threads.

Secondly, can you explain your problem? My guess: you need a SRF:
http://www.varlena.com/GeneralBits/26, chapter 'Returning Sets'.

HTH, Andreas
--
Andreas Kretschmer (Kontakt: siehe Header)
Heynitz: 035242/47215, D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
=== Schollglas Unternehmensgruppe ===

Sorry for that. I think this will help me resolve my problem. Thanks a lot
and sorry again

>From: "A. Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com>
>To: pgsql-novice(at)postgresql(dot)org
>Subject: Re: [NOVICE] returning Record type problem
>Date: Wed, 15 Feb 2006 13:18:30 +0100
>
>am 15.02.2006, um 12:03:29 +0000 mailte Luis Silva folgendes:
> >
> > sorry again. another question. When I return a Record value I only get
>the
> > first row of the result. What might be the problem? tks again
>
>Please:
>
>If you start a new thread (question), then start a new thread and don't
>change the subject from a old mail. Many people have mail-clients with
>the possibility to show threads.
>
>Secondly, can you explain your problem? My guess: you need a SRF:
>http://www.varlena.com/GeneralBits/26, chapter 'Returning Sets'.
>
>
>HTH, Andreas
>--
>Andreas Kretschmer (Kontakt: siehe Header)
>Heynitz: 035242/47215, D1: 0160/7141639
>GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
> === Schollglas Unternehmensgruppe ===
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: Don't 'kill -9' the postmaster

I there!! I'm having a problem. I know that it's possible to know the exact
ID of a table or a column using OIDs but, is it possible to know the ID from
a field?. for example I have a table named "identities" with 3 columns:
name,address and age and 3 users in that table:
richard | spain | 22
joe | france | 23
carol | italy | 24

for example, does the field "france" as a unique id that I could save and
get that info only with that id?

thanks a lot Luis

am 22.02.2006, um 18:32:14 +0000 mailte Luis Silva folgendes:
> I there!! I'm having a problem. I know that it's possible to know the exact
> ID of a table or a column using OIDs but, is it possible to know the ID
> from a field?. for example I have a table named "identities" with 3

If you create the table with oid, yes. And, you have the ctid.
http://www.postgresql.org/docs/faqs.FAQ.html -> 4.12 ctid

HTH, Andreas
--
Andreas Kretschmer (Kontakt: siehe Header)
Heynitz: 035242/47215, D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
=== Schollglas Unternehmensgruppe ===

I there. I having a problem. I have a table with 3 columns, identity,
implicit_group(int) and private. if insert, for exemple, one identity with
an implicit_group with the value '1' and a private 'aaaa' i need to guaranty
that i can't insert another identity with an implicity_group '1' and a
different private. How can I do this? tks a lot.

ps - Tks Andreas for responding to my other problem

On Thu, Feb 23, 2006 at 06:54:49PM +0000, Luis Silva wrote:
> I there. I having a problem. I have a table with 3 columns, identity,
> implicit_group(int) and private. if insert, for exemple, one identity with
> an implicit_group with the value '1' and a private 'aaaa' i need to
> guaranty that i can't insert another identity with an implicity_group '1'
> and a different private. How can I do this? tks a lot.

It's not clear exactly what you want to allow and prevent. Is it
acceptable to insert a different identity with the same implicit_group
and private as the first row? What about another row with the same
identity as the first row but with a different implicit_group and/or
private? It might be helpful if you could indicate whether each
of the following rows, inserted in the given order, should be
accepted or rejected and why:

1) 'ident1', 1, 'aaaa' -- first row, assumed to be accepted
2) 'ident1', 1, 'aaaa' -- duplicate of (1)
3) 'ident1', 1, 'bbbb' -- same ident and group as (1) but different private
4) 'ident1', 2, 'aaaa' -- same ident and private as (1) but different group
5) 'ident1', 2, 'bbbb' -- same ident as (1) but different group and private
6) 'ident2', 1, 'aaaa' -- different ident as (1) but same group and private
7) 'ident2', 1, 'bbbb' -- different ident and private as (1) but same group
8) 'ident2', 2, 'aaaa' -- different ident and group as (1) but same private
9) 'ident2', 2, 'bbbb' -- different ident, group, and private from (1)

It might also be helpful if you gave an abstract explanation of the
problem you're trying to solve. Sometimes that can suggest a schema
redesign.

--
Michael Fuhr

tks for answering.
Identity is the private key from the table. using your examples you could
have :
1) ident1,1,'aaaa'
2) ident2,1,'aaaa'
3) ident3,2,'aaaa'
4) ident4,3,'bbbb'

but not

5)identi5,1,'bbbb'

-->same implicit_group for different privates.
is it possible? tks again

>From: Michael Fuhr <mike(at)fuhr(dot)org>
>To: Luis Silva <lfs12(at)hotmail(dot)com>
>CC: pgsql-novice(at)postgresql(dot)org
>Subject: Re: [NOVICE] check problem
>Date: Thu, 23 Feb 2006 15:45:23 -0700
>
>On Thu, Feb 23, 2006 at 06:54:49PM +0000, Luis Silva wrote:
> > I there. I having a problem. I have a table with 3 columns, identity,
> > implicit_group(int) and private. if insert, for exemple, one identity
>with
> > an implicit_group with the value '1' and a private 'aaaa' i need to
> > guaranty that i can't insert another identity with an implicity_group
>'1'
> > and a different private. How can I do this? tks a lot.
>
>It's not clear exactly what you want to allow and prevent. Is it
>acceptable to insert a different identity with the same implicit_group
>and private as the first row? What about another row with the same
>identity as the first row but with a different implicit_group and/or
>private? It might be helpful if you could indicate whether each
>of the following rows, inserted in the given order, should be
>accepted or rejected and why:
>
>1) 'ident1', 1, 'aaaa' -- first row, assumed to be accepted
>2) 'ident1', 1, 'aaaa' -- duplicate of (1)
>3) 'ident1', 1, 'bbbb' -- same ident and group as (1) but different
>private
>4) 'ident1', 2, 'aaaa' -- same ident and private as (1) but different
>group
>5) 'ident1', 2, 'bbbb' -- same ident as (1) but different group and
>private
>6) 'ident2', 1, 'aaaa' -- different ident as (1) but same group and
>private
>7) 'ident2', 1, 'bbbb' -- different ident and private as (1) but same
>group
>8) 'ident2', 2, 'aaaa' -- different ident and group as (1) but same
>private
>9) 'ident2', 2, 'bbbb' -- different ident, group, and private from (1)
>
>It might also be helpful if you gave an abstract explanation of the
>problem you're trying to solve. Sometimes that can suggest a schema
>redesign.
>
>--
>Michael Fuhr
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: Don't 'kill -9' the postmaster

On Thu, Feb 23, 2006 at 23:52:31 +0000,
Luis Silva <lfs12(at)hotmail(dot)com> wrote:
>
> tks for answering.
> Identity is the private key from the table. using your examples you could
> have :
> 1) ident1,1,'aaaa'
> 2) ident2,1,'aaaa'
> 3) ident3,2,'aaaa'
> 4) ident4,3,'bbbb'
>
> but not
>
> 5)identi5,1,'bbbb'
>
> -->same implicit_group for different privates.
> is it possible? tks again

The above table should only have the ident and the group number. The group
number should have a foreign key reference to another table of which the
group number is the primary key and another column for private.
This will allow only one private for each group number.

is it possible to make a trigger that could evaluate all identities and
search for the implicit_group that we want to insert and match with that
private. if false return an error.

>From: Bruno Wolff III <bruno(at)wolff(dot)to>
>To: Luis Silva <lfs12(at)hotmail(dot)com>
>CC: mike(at)fuhr(dot)org, pgsql-novice(at)postgresql(dot)org
>Subject: Re: [NOVICE] check problem
>Date: Thu, 23 Feb 2006 18:52:32 -0600
>
>On Thu, Feb 23, 2006 at 23:52:31 +0000,
> Luis Silva <lfs12(at)hotmail(dot)com> wrote:
> >
> > tks for answering.
> > Identity is the private key from the table. using your examples you
>could
> > have :
> > 1) ident1,1,'aaaa'
> > 2) ident2,1,'aaaa'
> > 3) ident3,2,'aaaa'
> > 4) ident4,3,'bbbb'
> >
> > but not
> >
> > 5)identi5,1,'bbbb'
> >
> > -->same implicit_group for different privates.
> > is it possible? tks again
>
>The above table should only have the ident and the group number. The group
>number should have a foreign key reference to another table of which the
>group number is the primary key and another column for private.
>This will allow only one private for each group number.
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly

On Fri, Feb 24, 2006 at 10:09:29 +0000,
Luis Silva <lfs12(at)hotmail(dot)com> wrote:
>
> is it possible to make a trigger that could evaluate all identities and
> search for the implicit_group that we want to insert and match with that
> private. if false return an error.

Why do you want such a function? What are you really trying to do?

It is usually better to ask about how you might accomplish a high level task
than to ask about how to use some particular solution to that task, as you
may not have picked a very good way to do things.

>
> >From: Bruno Wolff III <bruno(at)wolff(dot)to>
> >To: Luis Silva <lfs12(at)hotmail(dot)com>
> >CC: mike(at)fuhr(dot)org, pgsql-novice(at)postgresql(dot)org
> >Subject: Re: [NOVICE] check problem
> >Date: Thu, 23 Feb 2006 18:52:32 -0600
> >
> >On Thu, Feb 23, 2006 at 23:52:31 +0000,
> > Luis Silva <lfs12(at)hotmail(dot)com> wrote:
> >>
> >> tks for answering.
> >> Identity is the private key from the table. using your examples you
> >could
> >> have :
> >> 1) ident1,1,'aaaa'
> >> 2) ident2,1,'aaaa'
> >> 3) ident3,2,'aaaa'
> >> 4) ident4,3,'bbbb'
> >>
> >> but not
> >>
> >> 5)identi5,1,'bbbb'
> >>
> >> -->same implicit_group for different privates.
> >> is it possible? tks again
> >
> >The above table should only have the ident and the group number. The group
> >number should have a foreign key reference to another table of which the
> >group number is the primary key and another column for private.
> >This will allow only one private for each group number.
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 1: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> > message can get through to the mailing list cleanly
>
>

Hi there, I'm having a problem, When I'm doing an update to one of the
fields of my table I need to know if it's changed.
For exemple I have the table with the columns
id,identity,registration_state.

Test:
id | identity | registration_state
1 | Joe | registered
2 | Tom | not_registered
3 | James | unregistered

when i do "update test set registration_state='registered'" I need to know
for which identities the registration_state was change (and then return it).
In this case, Tom and James.

I was thinking about using a trigger function for the updates, using
pl/pgsql (using before in the trigger). the problem is that I don't know how
to get the old registration_state when I have multiple rows affected. I
known that if I use OLD.registration_state it works for one rows. Can you
help for multiple? tks a lot in advance

On Monday 20 March 2006 05:23 am, Luis Silva saith:
> Hi there, I'm having a problem, When I'm doing an update to one of the
> fields of my table I need to know if it's changed.
> For exemple I have the table with the columns
> id,identity,registration_state.
>
> Test:
> id | identity | registration_state
> 1 | Joe | registered
> 2 | Tom | not_registered
> 3 | James | unregistered
>
> when i do "update test set registration_state='registered'" I need to know
> for which identities the registration_state was change (and then return
> it). In this case, Tom and James.

I believe that all three will be updated with the above statement.

>
> I was thinking about using a trigger function for the updates, using
> pl/pgsql (using before in the trigger). the problem is that I don't know
> how to get the old registration_state when I have multiple rows affected. I
> known that if I use OLD.registration_state it works for one rows. Can you
> help for multiple? tks a lot in advance
>

Define the BEFORE trigger so that it fires for each row as in:
CREATE TRIGGER name { BEFORE | AFTER } { event [ OR ... ] }
ON table [ FOR [ EACH ] { ROW | STATEMENT } ]
EXECUTE PROCEDURE funcname ( arguments )

Pick the ROW option. This will cause the trigger to fire for each row updated
in your statement. You are going to need another table called audit or
something so that your trigger can insert information about the change into
that table.

>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster

Hi, tks for answering. What you told is true, if I use the query "update
test set registration_state='registered'" all the rows will be affected.
That is the problem, I need to know only the rows that are different. So,
for that, I was thinking about using the trigger for each row. So according
to what you said, I could use a table to save the cases where the
Old.registration_state != New.registration_state. But that gives me another
problem, if multiple process access the same table that would be a problem.
Can you help me. tks

----- Original Message -----
From: "Terry Lee Tucker" <terry(at)esc1(dot)com>
To: <pgsql-novice(at)postgresql(dot)org>
Sent: Monday, March 20, 2006 10:35 AM
Subject: Re: [NOVICE] Trigger problem

> On Monday 20 March 2006 05:23 am, Luis Silva saith:
>> Hi there, I'm having a problem, When I'm doing an update to one of the
>> fields of my table I need to know if it's changed.
>> For exemple I have the table with the columns
>> id,identity,registration_state.
>>
>> Test:
>> id | identity | registration_state
>> 1 | Joe | registered
>> 2 | Tom | not_registered
>> 3 | James | unregistered
>>
>> when i do "update test set registration_state='registered'" I need to
>> know
>> for which identities the registration_state was change (and then return
>> it). In this case, Tom and James.
>
> I believe that all three will be updated with the above statement.
>
>>
>> I was thinking about using a trigger function for the updates, using
>> pl/pgsql (using before in the trigger). the problem is that I don't know
>> how to get the old registration_state when I have multiple rows affected.
>> I
>> known that if I use OLD.registration_state it works for one rows. Can
>> you
>> help for multiple? tks a lot in advance
>>
>
> Define the BEFORE trigger so that it fires for each row as in:
> CREATE TRIGGER name { BEFORE | AFTER } { event [ OR ... ] }
> ON table [ FOR [ EACH ] { ROW | STATEMENT } ]
> EXECUTE PROCEDURE funcname ( arguments )
>
> Pick the ROW option. This will cause the trigger to fire for each row
> updated
> in your statement. You are going to need another table called audit or
> something so that your trigger can insert information about the change
> into
> that table.
>
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 2: Don't 'kill -9' the postmaster
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>

Hi! Is it possible to know in a loop in pl/pgsql if the next element is de
last? exemple

DECLARE
asds Record;

BEGIN

FOR asds IN EXECUTE 'select id from user_table' LOOP

IF asds.id.next = last THEN
......
END IF;

END LOOP;

tks

am 05.04.2006, um 0:01:02 +0100 mailte Luis Silva folgendes:
> Hi! Is it possible to know in a loop in pl/pgsql if the next element is de
> last? exemple
>
> DECLARE
> asds Record;
>
> BEGIN
>
> FOR asds IN EXECUTE 'select id from user_table' LOOP
>
> IF asds.id.next = last THEN
> ......
> END IF;
>
> END LOOP;

You can use a cursor and walk with the cursor through the result.
(move last cursor_name -> you have the last id, then move first, then
your for ... loop and check the id if is it the last id)

Perhaps, there are better solutions...

HTH, Andreas
--
Andreas Kretschmer (Kontakt: siehe Header)
Heynitz: 035242/47215, D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
=== Schollglas Unternehmensgruppe ===

I there, is it possible to identify the transaction running, when using a
pg/sql function?
Here is my problem:

I have a trigger in the database for one of my tables when I do an update. I
was trying, for all the rows affected when I'm doing an update, to send a
NOTIFY to my code. the problem is that I realize that if the NOTIFYs are
inside the same transaction, the database only sends one. What I'm doing is
having a auxiliar table where I insert the information that I consult when I
received a NOTIFY, and then after I read that information in my code I just
delete it. the problem is that I've Concurrency and I dont want to use
SELECT FOR UPDATE. If I had another row inside that auxiliar table
identifying the transaction I could distinguish the information and when I
delete it, according to the transaction id.

can you help ? Luis

Hi there! I'm having a big problem. I'm creating a pl/pgsql function and I
want my function to return one table that I've created, but that info is
created by me:

-->Table

CREATE TABLE returnfunc
(
error bool NOT NULL,
result text NOT NULL,
name text
)
WITHOUT OIDS;

-->Pl/pgsql func
CREATE OR REPLACE FUNCTION validate(pub text)
RETURNS returnfunc AS
$BODY$
DECLARE
answer returnfunc
val_pub text;
asds Record;

BEGIN
val_priv:=$1;

EXECUTE 'SELECT count(*) FROM public_data WHERE
identity='||quote_literal(val_pub) INTO asds;

IF asds.count=0 THEN
answer.error='1';
answer.result='DIAMETER_ERROR_USER_UNKNOWN';
RETURN answer;
END IF;

RETURN NULL;
END;
....

the problem is that this is returning " (t,DIAMETER_ERROR_USER_UNKNOWN,) "
and I want to return this information in different columns, like when i'm
doing a select to the returnfunc. Is that possible? is there another away of
doing this? tks in advance

sorry, I was doing select validate('') and I need to do select * from
validate(''). sorry

>From: "Luis Silva" <lfs12(at)hotmail(dot)com>
>To: pgsql-novice(at)postgresql(dot)org
>Subject: [NOVICE] returning from a trigger
>Date: Tue, 18 Apr 2006 00:03:33 +0000
>
>
>Hi there! I'm having a big problem. I'm creating a pl/pgsql function and I
>want my function to return one table that I've created, but that info is
>created by me:
>
>-->Table
>
>CREATE TABLE returnfunc
>(
> error bool NOT NULL,
> result text NOT NULL,
> name text
>)
>WITHOUT OIDS;
>
>-->Pl/pgsql func
>CREATE OR REPLACE FUNCTION validate(pub text)
> RETURNS returnfunc AS
>$BODY$
>DECLARE
>answer returnfunc
>val_pub text;
>asds Record;
>
>BEGIN
> val_priv:=$1;
>
> EXECUTE 'SELECT count(*) FROM public_data WHERE
>identity='||quote_literal(val_pub) INTO asds;
>
> IF asds.count=0 THEN
> answer.error='1';
> answer.result='DIAMETER_ERROR_USER_UNKNOWN';
> RETURN answer;
> END IF;
>
>RETURN NULL;
>END;
>....
>
>the problem is that this is returning " (t,DIAMETER_ERROR_USER_UNKNOWN,) "
>and I want to return this information in different columns, like when i'm
>doing a select to the returnfunc. Is that possible? is there another away
>of doing this? tks in advance
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq

HI there! I'm doing some stress test in my db with 100.000 users (rows) in
one of my tables. I'm using indexes,vacuum, and other stuff but the db is
too slow. Is there any other mechanism to optimize the db? tks in advance

> HI there! I'm doing some stress test in my db with 100.000 users (rows) in
> one of my tables. I'm using indexes,vacuum, and other stuff but the db is
> too slow. Is there any other mechanism to optimize the db? tks in advance

This link should be a good resource for improving database preformance.
http://www.postgresql.org/docs/8.1/interactive/maintenance.html

Additional steps would be to "tune" your PostgreSQL configuration.
http://www.postgresql.org/docs/8.1/interactive/runtime-config-resource.html

There are additional links that describe what to do with these setting if you are interested.

Lastly, you could always upgrade your hardware. :-)

Regards,

Richard Broersma Jr.

* Luis Silva <lfs12(at)hotmail(dot)com> [2006-04-24 17:02:17 +0000]:

Hi Luis,

It seems that when you start a new topic, you reply to one of the
replies to your previous topic. My mail-reader threading has gone off
the right hand side for your last few posts.

Would you mind making sure the "In-Reply-To" header is blank when you
start a new topic.

Thanks,
Andy