Thread: RETURN QUERY generates error

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        :=
'SELECTstart, 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.

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

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
--------abc
(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
----abc
(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
>

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

> 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.