Re: Loading Array - Mailing list pgsql-general

On Fri, 17 May 2002 09:00:27 +1000
"Andrew Bartley" <abartley@evolvosystems.com> wrote:

> insert into test
> select '''{' || concatkey || '}''' from visitor where user_id = 477373
>
> returns
>
> Error: ' but expression is of type 'text'
>  You will need to rewrite or cast the expression (State:S1000, Native Code:
> 7)
>
> I'm sure I need to CAST the result...  But to what type..  I have tried lots
> of different things but still carn't work it out.

Hi, Andrew.

No matter what type you cast the result to, it seems like there's no chance
that it can be inserted into array's column; actually, I couldn't either.
But, if using a dynamic query in plpgsql, you would be able to insert.


CREATE OR REPLACE FUNCTION fn_visitor (int4) RETURNS boolean AS '
    DECLARE
        sql text';
        rec RECORD;
    BEGIN
        FOR rec IN SELECT * FROM visitor WHERE user_id = $1 LOOP
            sql := ''insert into test values(''''{''
                    || rec.concatkey
                    || ''}'''');'';
            EXECUTE sql;
            RAISE NOTICE ''% is inserted.'', rec.concatkey;
        END LOOP;
        RETURN true;
    END;
' language 'plpgsql' ;


SELECT fn_visitor(477373);



Regards,
Masaru Sugawara