http://www.postgresql.org/docs/8.0/static/xfunc-sql.html

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;

SELECT test_func();

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 15:58:20 +0100 mailte Nigel Horne folgendes:

I can't work out from that how to return more than one value.

17:35 < rtfm_please> For information about srf
17:35 < rtfm_please> see http://techdocs.postgresql.org/guides/SetReturningFunctions
17:35 < rtfm_please> or http://www.postgresql.org/docs/current/static/xfunc-sql.html#AEN28835

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

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 22/8/05 10:19 am, "Nigel Horne" <njh@bandsman.co.uk> 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.

Seeing as there will only ever be one row in this table, do I need the
SETOF field?

Try it and see.... I think you don't need it for a single row.

adam

--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

On Mon, 2005-08-22 at 10:29, Adam Witney wrote:

On 22/8/05 10:19 am, "Nigel Horne" <njh@bandsman.co.uk> 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.

Seeing as there will only ever be one row in this table, do I need the
SETOF field?

Try it and see.... I think you don't need it for a single row.

Another problem comes to mind, how can I make this multi-thread
safe? Since the return values from the function are stored in the
database I need to ensure that simultaneous calls to the function are
stored independently in the temporary table.

adam

-Nigel

On Fri, 2005-08-19 at 16:34, A. Kretschmer wrote:

am 19.08.2005, um 15:58:20 +0100 mailte Nigel Horne folgendes:

I can't work out from that how to return more than one value.

17:35 < rtfm_please> For information about srf
17:35 < rtfm_please> see http://techdocs.postgresql.org/guides/SetReturningFunctions
17:35 < rtfm_please> or http://www.postgresql.org/docs/current/static/xfunc-sql.html#AEN28835

How does that help with my problem? I seems to discuss returning more
than one row of a table which is not the question I asked.

Regards, Andreas

-Nigel