From: Yura Gal <yuragal@gmail.com>
To: pgsql-sql@postgresql.org
Subject: RETURN QUERY generates error
Date: Thu, 6 Mar 2008 10:45:11 +0300
Message-ID: <3b6c69d80803052345g31af3b74k6b8878c65197d621@mail.gmail.com> (raw)
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.
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: yuragal@gmail.com
Subject: Re: RETURN QUERY generates error
In-Reply-To: <3b6c69d80803052345g31af3b74k6b8878c65197d621@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