Re: RETURN QUERY generates error

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(at)gmail(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.org&extra=pgsql-sql
>