pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRETURN QUERY generates error
5+ messages / 4 participants
[nested] [flat]
* RETURN QUERY generates error
@ 2008-03-06 07:45 Yura Gal <yuragal@gmail.com>
2008-03-06 08:24 ` Re: RETURN QUERY generates error Richard Huxton <dev@archonet.com>
2008-03-06 08:40 ` Re: RETURN QUERY generates error Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 2 replies; 5+ messages in thread
From: Yura Gal @ 2008-03-06 07:45 UTC (permalink / raw)
To: pgsql-sql
Here is my function:
CREATE OR REPLACE FUNCTION "hg18"."get_genomeseq" (_chr varchar,
_byblocks boolean, _starts integer [], _ends integer []) RETURNS SETOF
text AS
$body$
DECLARE
_start integer;
_end integer;
_sequence text[];
_seq50 RECORD;
_seq text;
_q text;
BEGIN
FOR i IN 1..array_upper(_starts, 1) LOOP
_start := _starts[i];
_end := _ends[i];
_q :=
'SELECT start, sequence ' ||
'FROM hg18.genome ' ||
$$WHERE chr = 'chr' || '$$ || _chr::varchar || $$' $$ ||
'AND start >= floor(' || _start || '/50)*50 ' ||
'AND start < ' || _end;
--RAISE NOTICE 'Query is %', _q;
FOR _seq50 IN EXECUTE _q LOOP
IF _seq50.start < _start THEN
_sequence[i] := substring(_seq50.sequence, _start%_seq50.start);
ELSEIF _seq50.start >= _start AND _seq50.start + 49 <= _end THEN
_sequence[i] := _sequence[i] || _seq50.sequence;
ELSE
_sequence[i] := _sequence[i]
|| substring(_seq50.sequence, 1, _end%_seq50.start);
END IF;
END LOOP;
END LOOP;
IF _byblocks IS TRUE THEN
RETURN QUERY SELECT
regexp_split_to_table(array_to_string(_sequence, ','), E',');
ELSE
RETURN QUERY SELECT array_to_string(_sequence, '');
END IF;
RETURN;
END;
$body$
LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER;
all is fine until the last IF. Both RETURN QUERY blocks generate
error: 'syntax error at or near SELECT ...' I feel that something
wrong with casting _sequence var but I can't figure out the exact
point.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: RETURN QUERY generates error
2008-03-06 07:45 RETURN QUERY generates error Yura Gal <yuragal@gmail.com>
@ 2008-03-06 08:24 ` Richard Huxton <dev@archonet.com>
2008-03-06 18:14 ` Re: RETURN QUERY generates error Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 5+ messages in thread
From: Richard Huxton @ 2008-03-06 08:24 UTC (permalink / raw)
To: Yura Gal <yuragal@gmail.com>; +Cc: pgsql-sql
Yura Gal wrote:
>
> IF _byblocks IS TRUE THEN
> RETURN QUERY SELECT
> regexp_split_to_table(array_to_string(_sequence, ','), E',');
> ELSE
> RETURN QUERY SELECT array_to_string(_sequence, '');
> END IF;
> RETURN;
> END;
> $body$
> LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER;
>
> all is fine until the last IF. Both RETURN QUERY blocks generate
> error: 'syntax error at or near SELECT ...' I feel that something
> wrong with casting _sequence var but I can't figure out the exact
> point.
Can't think why it's not happy - if I create an empty table to go with
it, it runs here. If you replace them with RETURN QUERY SELECT '' does
that make the error go away?
Oh, and are you sure you mean IMMUTABLE? That's only true if hg18.genome
is a static table.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: RETURN QUERY generates error
2008-03-06 07:45 RETURN QUERY generates error Yura Gal <yuragal@gmail.com>
2008-03-06 08:24 ` Re: RETURN QUERY generates error Richard Huxton <dev@archonet.com>
@ 2008-03-06 18:14 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-03-07 13:23 ` Re: RETURN QUERY generates error Yura Gal <yuragal@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2008-03-06 18:14 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: Yura Gal <yuragal@gmail.com>; pgsql-sql
Richard Huxton <dev@archonet.com> writes:
> Yura Gal wrote:
>> all is fine until the last IF. Both RETURN QUERY blocks generate
>> error: 'syntax error at or near SELECT ...' I feel that something
>> wrong with casting _sequence var but I can't figure out the exact
>> point.
> Can't think why it's not happy - if I create an empty table to go with
> it, it runs here.
The error sounds suspiciously like what would happen if you tried to
use RETURN QUERY in a pre-8.3 version.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: RETURN QUERY generates error
2008-03-06 07:45 RETURN QUERY generates error Yura Gal <yuragal@gmail.com>
2008-03-06 08:24 ` Re: RETURN QUERY generates error Richard Huxton <dev@archonet.com>
2008-03-06 18:14 ` Re: RETURN QUERY generates error Tom Lane <tgl@sss.pgh.pa.us>
@ 2008-03-07 13:23 ` Yura Gal <yuragal@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Yura Gal @ 2008-03-07 13:23 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Richard Huxton <dev@archonet.com>; pgsql-sql
> The error sounds suspiciously like what would happen if you tried to
> use RETURN QUERY in a pre-8.3 version.
>
> regards, tom lane
>
Thanks a lot to all. Actually there were a mass of errors in my
function. Now I rewrite it so as it works. It would be much more
complicated to achieve my goal without your advices.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: RETURN QUERY generates error
2008-03-06 07:45 RETURN QUERY generates error Yura Gal <yuragal@gmail.com>
@ 2008-03-06 08:40 ` Pavel Stehule <pavel.stehule@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: Pavel Stehule @ 2008-03-06 08:40 UTC (permalink / raw)
To: Yura Gal <yuragal@gmail.com>; +Cc: pgsql-sql
Hello
I did simple test:
postgres=# create or replace function unpack(anyarray) returns setof
anyelement as $$ select $1[i] from generate_series(array_lower($1,1),
array_upper($1,1)) g(i); $$ language sql;
CREATE FUNCTION
Time: 20,133 ms
postgres=# select unpack(string_to_array('a,b,c',','));
unpack
--------
a
b
c
(3 rows)
postgres=# create or replace function fx(text) returns setof text as
$$begin return query select unpack(string_to_array($1,',')); end; $$
language plpgsql;
CREATE FUNCTION
Time: 3,805 ms
postgres=# select * from fx('a,b,c');
fx
----
a
b
c
(3 rows)
Pavel
On 06/03/2008, Yura Gal <yuragal@gmail.com> wrote:
> Here is my function:
>
> CREATE OR REPLACE FUNCTION "hg18"."get_genomeseq" (_chr varchar,
> _byblocks boolean, _starts integer [], _ends integer []) RETURNS SETOF
> text AS
> $body$
> DECLARE
> _start integer;
> _end integer;
> _sequence text[];
> _seq50 RECORD;
> _seq text;
> _q text;
> BEGIN
> FOR i IN 1..array_upper(_starts, 1) LOOP
> _start := _starts[i];
> _end := _ends[i];
> _q :=
> 'SELECT start, sequence ' ||
> 'FROM hg18.genome ' ||
> $$WHERE chr = 'chr' || '$$ || _chr::varchar || $$' $$ ||
> 'AND start >= floor(' || _start || '/50)*50 ' ||
> 'AND start < ' || _end;
> --RAISE NOTICE 'Query is %', _q;
> FOR _seq50 IN EXECUTE _q LOOP
> IF _seq50.start < _start THEN
> _sequence[i] := substring(_seq50.sequence, _start%_seq50.start);
> ELSEIF _seq50.start >= _start AND _seq50.start + 49 <= _end THEN
> _sequence[i] := _sequence[i] || _seq50.sequence;
> ELSE
> _sequence[i] := _sequence[i]
> || substring(_seq50.sequence, 1, _end%_seq50.start);
> END IF;
> END LOOP;
> END LOOP;
>
> IF _byblocks IS TRUE THEN
> RETURN QUERY SELECT
> regexp_split_to_table(array_to_string(_sequence, ','), E',');
> ELSE
> RETURN QUERY SELECT array_to_string(_sequence, '');
> END IF;
> RETURN;
> END;
> $body$
> LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER;
>
> all is fine until the last IF. Both RETURN QUERY blocks generate
> error: 'syntax error at or near SELECT ...' I feel that something
> wrong with casting _sequence var but I can't figure out the exact
> point.
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.org&extra=pgsql-sql
>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2008-03-07 13:23 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-03-06 07:45 RETURN QUERY generates error Yura Gal <yuragal@gmail.com>
2008-03-06 08:24 ` Richard Huxton <dev@archonet.com>
2008-03-06 18:14 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-03-07 13:23 ` Yura Gal <yuragal@gmail.com>
2008-03-06 08:40 ` Pavel Stehule <pavel.stehule@gmail.com>
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