Re: RETURN QUERY generates error - Mailing list 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
--------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
>