Обсуждение: puzzled by SELECT INTO
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
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
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