pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedMultiple return values and assignment
3+ messages / 2 participants
[nested] [flat]
* Multiple return values and assignment
@ 2009-04-25 11:06 Leif B. Kristensen <leif@solumslekt.org>
0 siblings, 1 reply; 3+ messages in thread
From: Leif B. Kristensen @ 2009-04-25 11:06 UTC (permalink / raw)
To: pgsql-sql
I've got a function that returns both an integer and a string as a
user-defined composite type int_text:
-- CREATE TYPE int_text AS (number INTEGER, string TEXT);
Basically, the function does some heuristics to extract a sort order
number from a text, and conditionally modify the text:
CREATE OR REPLACE FUNCTION get_sort(INTEGER, INTEGER, TEXT)
RETURNS int_text AS $$
-- parse text to infer sort order; factored out of add_source() below
-- because the functionality needs to be accessed directly from PHP as
-- well, and this approach eliminates previous duplication of code.
-- CREATE TYPE int_text AS (number INTEGER, string TEXT)
DECLARE
par_id INTEGER = $1;
srt INTEGER = $2;
txt TEXT = $3;
sort_text int_text;
BEGIN
-- default condition: if nothing is modified, return input values
sort_text.number := srt;
sort_text.string := txt;
-- 1) use page number for sort order
-- (low priority, may be overridden)
IF srt = 1 THEN -- don't apply this rule unless sort = default
IF txt SIMILAR TO E'%side \\d+%' THEN
-- use page number as sort order
SELECT SUBSTR(SUBSTRING(txt, E'side \\d+'), 5,
LENGTH(SUBSTRING(txt, E'side \\d+')) -4)::INTEGER
INTO sort_text.number;
END IF;
END IF;
-- 2) use ^#(\d+) for sort order
IF txt SIMILAR TO E'#\\d+%' THEN
SELECT SUBSTR(SUBSTRING(txt, E'#\\d+'), 2,
LENGTH(SUBSTRING(txt, E'#\\d+')) -1)::INTEGER
INTO sort_text.number;
-- strip #number from text
sort_text.string := REGEXP_REPLACE(txt, E'^#\\d+ ', '');
END IF;
-- 3) increment from max(sort_order) of source group
IF txt LIKE '++ %' THEN
SELECT MAX(sort_order) + 1
FROM sources
WHERE get_source_gp(source_id) =
(SELECT parent_id FROM sources WHERE source_id = par_id)
INTO sort_text.number;
-- strip symbol from text
sort_text.string := REPLACE(txt, '++ ', '');
END IF;
RETURN sort_text;
END
$$ LANGUAGE plpgsql STABLE;
To use the two values in an other function where I've declared a
variable sort_text of type int_text, I do like this:
SELECT number, string FROM get_sort(par_id, srt, txt) INTO sort_text;
srt := sort_text.number;
txt := sort_text.string;
But I feel it's a little awkward. Is there a more elegant way to do it?
I can't run the get_sort() function twice, because it modifies its
input values.
--
Leif Biberg Kristensen | Registered Linux User #338009
Me And My Database: http://solumslekt.org/blog/
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Multiple return values and assignment
@ 2009-04-27 08:48 Jasen Betts <jasen@xnet.co.nz>
parent: Leif B. Kristensen <leif@solumslekt.org>
0 siblings, 1 reply; 3+ messages in thread
From: Jasen Betts @ 2009-04-27 08:48 UTC (permalink / raw)
To: pgsql-sql
On 2009-04-25, Leif B. Kristensen <leif@solumslekt.org> wrote:
> I've got a function that returns both an integer and a string as a
> user-defined composite type int_text:
>
> -- CREATE TYPE int_text AS (number INTEGER, string TEXT);
>
> Basically, the function does some heuristics to extract a sort order
> number from a text, and conditionally modify the text:
>
> CREATE OR REPLACE FUNCTION get_sort(INTEGER, INTEGER, TEXT)
> RETURNS int_text AS $$
...
> To use the two values in an other function where I've declared a
> variable sort_text of type int_text, I do like this:
>
> SELECT number, string FROM get_sort(par_id, srt, txt) INTO sort_text;
the above is equivalent to
sort_text = get_sort(par_id, srt, txt);
> srt := sort_text.number;
> txt := sort_text.string;
> But I feel it's a little awkward. Is there a more elegant way to do it?
SELECT * FROM get_sort(par_id, srt, txt) INTO srt,txt;
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Multiple return values and assignment
@ 2009-04-27 19:37 Leif B. Kristensen <leif@solumslekt.org>
parent: Jasen Betts <jasen@xnet.co.nz>
0 siblings, 0 replies; 3+ messages in thread
From: Leif B. Kristensen @ 2009-04-27 19:37 UTC (permalink / raw)
To: pgsql-sql
On Monday 27. April 2009, Jasen Betts wrote:
>SELECT * FROM get_sort(par_id, srt, txt) INTO srt,txt;
Thank you very much! That saved me from one composite variable
declaration and two superfluous lines of code. I've settled for
SELECT number, string FROM get_sort(par_id, srt, txt) INTO srt, txt;
as that seems a little tidier.
I probably should have thought of it myself, but I wrongly assumed that
it was impossible to do multiple assignments like this in plpgsql.
--
Leif Biberg Kristensen | Registered Linux User #338009
Me And My Database: http://solumslekt.org/blog/
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2009-04-27 19:37 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-04-25 11:06 Multiple return values and assignment Leif B. Kristensen <leif@solumslekt.org>
2009-04-27 08:48 ` Jasen Betts <jasen@xnet.co.nz>
2009-04-27 19:37 ` Leif B. Kristensen <leif@solumslekt.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox