DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=Ng1LS3krpI2r3Icm9012RdFrbx/SMdWveJcyJeDg0Tg8+Qac3rp0ICko7RfUMibJhAPdkks/hMc84Ic0Uu/uuGJ23+d/cXesYkDnYmDXd8w1Nq4lgretbnYmzBwDmDe1/+JV1zf4yi/EJI3UlCnjslwO81DhnonI8SZW6YjlsjU=
Message-ID: <162867790803060040w3b61b7f1ge883998d3562ba9c@mail.gmail.com>
Date: Thu, 6 Mar 2008 09:40:47 +0100
From: "Pavel Stehule" <pavel.stehule@gmail.com>
To: "Yura Gal" <yuragal@gmail.com>
Subject: Re: RETURN QUERY generates error
Cc: pgsql-sql@postgresql.org
In-Reply-To: <3b6c69d80803052345g31af3b74k6b8878c65197d621@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <3b6c69d80803052345g31af3b74k6b8878c65197d621@mail.gmail.com>

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
>