Re: RETURN QUERY generates error - Mailing list pgsql-sql

From Pavel Stehule
Subject Re: RETURN QUERY generates error
Date
Msg-id 162867790803060040w3b61b7f1ge883998d3562ba9c@mail.gmail.com
Whole thread Raw
In response to RETURN QUERY generates error  ("Yura Gal" <yuragal@gmail.com>)
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
>


pgsql-sql by date:

Previous
From: Richard Huxton
Date:
Subject: Re: RETURN QUERY generates error
Next
From: Tom Lane
Date:
Subject: Re: RETURN QUERY generates error