From: Pavel Stehule <pavel.stehule@gmail.com>
To: Yura Gal <yuragal@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: RETURN QUERY generates error
Date: Thu, 6 Mar 2008 09:40:47 +0100
Message-ID: <162867790803060040w3b61b7f1ge883998d3562ba9c@mail.gmail.com> (raw)
In-Reply-To: <3b6c69d80803052345g31af3b74k6b8878c65197d621@mail.gmail.com>
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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: pavel.stehule@gmail.com, yuragal@gmail.com
Subject: Re: RETURN QUERY generates error
In-Reply-To: <162867790803060040w3b61b7f1ge883998d3562ba9c@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox