Thread:
Hi,
I’m currently using triggers with postgresql ver. 7.4.6 on Fedora Core 3. I’ve implemented a dynamic library in `C` that receives all registered trigger. I’m having a hard time right now since my trigger function is executed even before that any transaction is committed (even if the trigger is set to trig AFTER).
Here’s my question: Is it possible to create triggers that are going to be executed only after a committed transaction?
Thanks!
Michel
On Fri, 2005-08-19 at 16:30, Adam Witney wrote: > > I can't work out from that how to return more than one value. > > Hi Nigel, > > Add SETOF to your function like so: Hmm. Ta. Sorry for all the newbie questions, but SQL isn't something I know anything about, but any work's work at the moment! -Nigel
On Fri, 2005-08-19 at 16:30, Adam Witney wrote: > > I can't work out from that how to return more than one value. > > Hi Nigel, > > Add SETOF to your function like so: > > CREATE TABLE test (id int); > INSERT INTO test VALUES(1); > INSERT INTO test VALUES(2); > > CREATE FUNCTION test_func() RETURNS SETOF integer AS ' > SELECT id FROM test; > ' LANGUAGE SQL; What if one value I want to return is an integer, and another one is a string? > Cheers > > Adam -Nigel
On 19/8/05 4:38 pm, "Nigel Horne" <njh@bandsman.co.uk> wrote:
> On Fri, 2005-08-19 at 16:30, Adam Witney wrote:
>>> I can't work out from that how to return more than one value.
>>
>> Hi Nigel,
>>
>> Add SETOF to your function like so:
>>
>> CREATE TABLE test (id int);
>> INSERT INTO test VALUES(1);
>> INSERT INTO test VALUES(2);
>>
>> CREATE FUNCTION test_func() RETURNS SETOF integer AS '
>> SELECT id FROM test;
>> ' LANGUAGE SQL;
>
> What if one value I want to return is an integer, and another one is a
> string?
Ah you want to return a record I suppose?
CREATE TABLE test (id int, name text);
INSERT INTO test VALUES(1, 'me');
INSERT INTO test VALUES(2, 'you');
CREATE FUNCTION test_func() RETURNS SETOF record AS '
SELECT id, name FROM test;
' LANGUAGE SQL;
SELECT * FROM test_func() AS (id int, name text);
id | name
----+------
1 | me
2 | you
Cheers
Adam
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
am 19.08.2005, um 16:38:20 +0100 mailte Nigel Horne folgendes: > On Fri, 2005-08-19 at 16:30, Adam Witney wrote: > > > I can't work out from that how to return more than one value. > > > > Hi Nigel, > > > > Add SETOF to your function like so: > > > > CREATE TABLE test (id int); > > INSERT INTO test VALUES(1); > > INSERT INTO test VALUES(2); > > > > CREATE FUNCTION test_func() RETURNS SETOF integer AS ' > > SELECT id FROM test; > > ' LANGUAGE SQL; > > What if one value I want to return is an integer, and another one is a > string? Create a new type. For instance: (sorry, comments in german) ,---- | create type saldeninfo as (kontonr bigint, | zugang numeric(10,2), | abgang numeric(10,2), | zeitpunkt timestamp, | saldo numeric(10,2)); | | -- | -- Nun die Funktion, sie ist in der Sprache SQL definiert | -- | create or replace function saldeninfo( int ) returns setof saldeninfo as | $$ | select reset_saldo(); | select kontonr, case when typ = 'Z' then wert when typ = 'A' then NULL END as zugang, | case when typ = 'Z' then NULL when typ = 'A' then wert end as abgang, | ts, | prev_saldo(get_saldo(typ, wert))::numeric(10,2) as saldo | from buchungen where kontonr = $1; | $$ | language sql; | `---- Regards, Andreas -- Andreas Kretschmer (Kontakt: siehe Header) Heynitz: 035242/47212, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net === Schollglas Unternehmensgruppe ===
Hi, I have a text field that has some email addresses that are each on a new line by a CRLF. I want to replace the CRLF with a comma so I can use the email addresses in another app, so I thought I would do this: thearray = replace(mandi_notifications,'/r/n',','); but it does not work. Does anyone know if it is possible to replace a CRLF in a string with PG built in functions? I would rather not use Perl if possible. Thanks, Tony
Adam Witney <awitney@sgul.ac.uk> writes:
> Ah you want to return a record I suppose?
> CREATE TABLE test (id int, name text);
> INSERT INTO test VALUES(1, 'me');
> INSERT INTO test VALUES(2, 'you');
> CREATE FUNCTION test_func() RETURNS SETOF record AS '
> SELECT id, name FROM test;
> ' LANGUAGE SQL;
Or better, "RETURNS SETOF test", so you don't have to describe the
output record type every time you call it.
regards, tom lane
Tony Caduto <tony_caduto@amsoftwaredesign.com> writes:
> I want to replace the CRLF with a comma so I can use the email
> addresses in another app, so I thought I would do this:
> thearray = replace(mandi_notifications,'/r/n',',');
> but it does not work.
I think you wanted backslashes not forward slashes.
regards, tom lane
Is it possible to write c style functions with Free Pascal? Thanks, Tony
On Fri, Aug 19, 2005 at 11:11:31AM -0500, Tony Caduto wrote: > I want to replace the CRLF with a comma so I can use the email > addresses in another app, so I thought I would do this: > > thearray = replace(mandi_notifications,'/r/n',','); > > but it does not work. Your slashes are leaning the wrong direction. Try this: thearray := replace(mandi_notifications, '\r\n', ','); Sometimes the number of backslashes (\) matters; see "Tips for Developing in PL/pgSQL" in the documentation for discussion. -- Michael Fuhr
am 19.08.2005, um 11:34:52 -0500 mailte Tony Caduto folgendes: > Is it possible to write c style functions with Free Pascal? Please, if you open a new subject, then open also a new thread in this email-list. Your messages are always in a wrong thread. Regards, Andreas -- Andreas Kretschmer (Kontakt: siehe Header) Heynitz: 035242/47212, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net === Schollglas Unternehmensgruppe ===
What the heck are you talking about? It was a brand new subject, it was not part of any thread. A. Kretschmer wrote: >am 19.08.2005, um 11:34:52 -0500 mailte Tony Caduto folgendes: > > >>Is it possible to write c style functions with Free Pascal? >> >> > >Please, if you open a new subject, then open also a new thread in this >email-list. Your messages are always in a wrong thread. > > >Regards, Andreas > > -- Tony Caduto http://www.amsoftwaredesign.com Home of PG Lightning Admin for Postgresql 8.x
Tony Caduto <tony_caduto@amsoftwaredesign.com> writes: > What the heck are you talking about? It was a brand new subject, it > was not part of any thread. It had a References: header referring to a previous message. Most mailers will honor this even if th esubject changes, and keep the message in the existing thread. -Doug
On Sat, Aug 20, 2005 at 09:00:56AM -0500, Tony Caduto wrote: > What the heck are you talking about? It was a brand new subject, it was > not part of any thread. Are you starting new threads by replying to messages and then changing the subject? The References and In-Reply-To headers in your messages suggest as much. For example, you recently started a thread with a subject of "Question about the NAME type used in pg_proc and pg_class". That message has the following headers: References: <43005482.7020105@pbnet.dk> <43005CBD.5070703@archonet.com> <43006480.4000409@pbnet.dk> <25298.1124113567@sss.pgh.pa.us> In-Reply-To: <25298.1124113567@sss.pgh.pa.us> These headers imply that you replied to Tom Lane's message in the "Optimizing query" thread (message 25298.1124113567@sss.pgh.pa.us) and then you changed the Subject header. When you do that, some threaded displays will display your message in the thread you replied to because those headers say that the message is part of that thread. For example: http://archives.postgresql.org/pgsql-general/2005-08/thrd2.php#00718 If you want to start a new thread, use your mail client's equivalent of "New Message" instead of "Reply To". If you *are* using "New Message" then your mail client (Thunderbird 1.0.2 on Windows, or so it claims) appears to be broken. -- Michael Fuhr
On Fri, 2005-08-19 at 17:29, Tom Lane wrote: > Adam Witney <awitney@sgul.ac.uk> writes: > > Ah you want to return a record I suppose? > > > CREATE TABLE test (id int, name text); > > INSERT INTO test VALUES(1, 'me'); > > INSERT INTO test VALUES(2, 'you'); > > > CREATE FUNCTION test_func() RETURNS SETOF record AS ' > > SELECT id, name FROM test; > > ' LANGUAGE SQL; > > Or better, "RETURNS SETOF test", so you don't have to describe the > output record type every time you call it. It strikes me that there are two problems with this approach: 1) It stores the return values in the database, that seems a waste 2) It's slightly more complicated in that I have to delete the return values from the previous call before inserting the return values from this call, making it even more complex and slow. > > regards, tom lane -Nigel
On Mon, 22 Aug 2005, Nigel Horne wrote: > On Fri, 2005-08-19 at 17:29, Tom Lane wrote: > > Adam Witney <awitney@sgul.ac.uk> writes: > > > Ah you want to return a record I suppose? > > > > > CREATE TABLE test (id int, name text); > > > INSERT INTO test VALUES(1, 'me'); > > > INSERT INTO test VALUES(2, 'you'); > > > > > CREATE FUNCTION test_func() RETURNS SETOF record AS ' > > > SELECT id, name FROM test; > > > ' LANGUAGE SQL; > > > > Or better, "RETURNS SETOF test", so you don't have to describe the > > output record type every time you call it. > > It strikes me that there are two problems with this approach: > > 1) It stores the return values in the database, that seems a waste It shouldn't. It only uses the table's type to describe the type output of the function, not for actual storage.
Nigel Horne <njh@bandsman.co.uk> writes:
> It strikes me that there are two problems with this approach:
> 1) It stores the return values in the database, that seems a waste
> 2) It's slightly more complicated in that I have to delete the
> return values from the previous call before inserting the return
> values from this call, making it even more complex and slow.
You've misunderstood this completely. We are not storing anything
essential in the table, we're just using its rowtype to describe the
function's composite-type result.
Personally I would have written the example using a composite type
to make this more clear:
CREATE TYPE test_func_type AS (id int, name text);
CREATE FUNCTION test_func() RETURNS SETOF test_func_type AS $$
SELECT 1, 'me' UNION ALL SELECT 2, 'you'
$$ LANGUAGE sql;
select * from test_func();
id | name
----+------
1 | me
2 | you
(2 rows)
regards, tom lane