pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedpuzzled by SELECT INTO
3+ messages / 2 participants
[nested] [flat]
* puzzled by SELECT INTO
@ 2007-10-30 20:43 Gerardo Herzig <gherzig@fmed.uba.ar>
0 siblings, 1 reply; 3+ messages in thread
From: Gerardo Herzig @ 2007-10-30 20:43 UTC (permalink / raw)
To: pgsql-sql
Hi all. Im having some trouble here that cannot understand.
Consider this function:
CREATE OR REPLACE FUNCTION read_words(bigint, varchar)
returns varchar
as
$$
declare
returnValue varchar ;
BEGIN
select * into returnValue from array_to_string(array(select word
from words where page_id=$1 and word_position in ($2)), ' ');
return returnValue;
END;
$$ language plpgsql;
So far, so good. But...
select * from read_words(99466::bigint, '2994,2995,2996');
read_words
------------
(1 row)
But...if i do a
select * from array_to_string(array(select word from words where
page_id=99466 and word_position in (2994,2995,2996)), ' ')
array_to_string
-----------------------------
man page inside
Means that the query itself seems OK, but something in the SELECT INTO
thing is not working to me.
Mmmm...i guess is not that. I just make the sql version of that function
CREATE OR REPLACE FUNCTION read_words(bigint, varchar)
returns varchar
as
$$
select * from array_to_string(array(select word from words where
page_id=$1 and word_position in ($2)), ' ');
$$
language sql;
with the same (NULL) results....Looks like im having some mistake near
'and word_position in ($2)...'
Wreird enough to me, need some advice plz!
Thanks!
Gerardo
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: puzzled by SELECT INTO
@ 2007-10-30 20:57 Rodrigo De León <rdeleonp@gmail.com>
parent: Gerardo Herzig <gherzig@fmed.uba.ar>
0 siblings, 1 reply; 3+ messages in thread
From: Rodrigo De León @ 2007-10-30 20:57 UTC (permalink / raw)
To: Gerardo Herzig <gherzig@fmed.uba.ar>; +Cc: pgsql-sql
On 10/30/07, Gerardo Herzig <gherzig@fmed.uba.ar> wrote:
> Wreird enough to me, need some advice plz!
CREATE OR REPLACE FUNCTION READ_WORDS(BIGINT, INT[])
RETURNS VARCHAR
AS
$$
DECLARE
RETURNVALUE VARCHAR;
BEGIN
SELECT ARRAY_TO_STRING(ARRAY(
SELECT WORD
FROM WORDS WHERE PAGE_ID=$1
AND WORD_POSITION = ANY ($2)
), ' ') INTO RETURNVALUE;
RETURN RETURNVALUE;
END;
$$ LANGUAGE PLPGSQL;
SELECT READ_WORDS(99466, '{2994,2995,2996}');
See:
http://www.postgresql.org/docs/8.2/static/arrays.html
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: puzzled by SELECT INTO
@ 2007-10-31 12:28 Gerardo Herzig <gherzig@fmed.uba.ar>
parent: Rodrigo De León <rdeleonp@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Gerardo Herzig @ 2007-10-31 12:28 UTC (permalink / raw)
To: Rodrigo De León <rdeleonp@gmail.com>; +Cc: pgsql-sql
Rodrigo De León wrote:
>On 10/30/07, Gerardo Herzig <gherzig@fmed.uba.ar> wrote:
>
>
>>Wreird enough to me, need some advice plz!
>>
>>
>
>CREATE OR REPLACE FUNCTION READ_WORDS(BIGINT, INT[])
>RETURNS VARCHAR
>AS
>$$
> DECLARE
> RETURNVALUE VARCHAR;
> BEGIN
> SELECT ARRAY_TO_STRING(ARRAY(
> SELECT WORD
> FROM WORDS WHERE PAGE_ID=$1
> AND WORD_POSITION = ANY ($2)
> ), ' ') INTO RETURNVALUE;
> RETURN RETURNVALUE;
> END;
>$$ LANGUAGE PLPGSQL;
>
>SELECT READ_WORDS(99466, '{2994,2995,2996}');
>
>See:
>http://www.postgresql.org/docs/8.2/static/arrays.html
>
>
>
>
Mmmm, yes, that make perfect sense. I did 'resolve' the previous
situation by using EXECUTE, i will try your solution now.
Gracias Rodrigo.
Gerardo
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2007-10-31 12:28 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-10-30 20:43 puzzled by SELECT INTO Gerardo Herzig <gherzig@fmed.uba.ar>
2007-10-30 20:57 ` Rodrigo De León <rdeleonp@gmail.com>
2007-10-31 12:28 ` Gerardo Herzig <gherzig@fmed.uba.ar>
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