Re: array in function - Mailing list pgsql-sql

From Pavel Stehule
Subject Re: array in function
Date
Msg-id CAFj8pRCDne=w8k2+Cu32GDU7in-Tkn8JQzg7ZvqcQMJ55DnCSA@mail.gmail.com
Whole thread Raw
In response to array in function  (Pena Kupen <kupen@wippies.fi>)
List pgsql-sql
Hello

pls, try

EXECUTE 'SELECT 1 FROM types WHERE type_id ANY($1) ' INTO hasValue USING _list;


Regards

Pavel

p.s. newer try to merge variables to SQL string without sanitization - your code is SQL injection vulnerable - and doesn't work


2014-02-24 9:42 GMT+01:00 Pena Kupen <kupen@wippies.fi>:
Hi,

I have a problem with function, where I want to use execute and create sql for it.

My table is:
create table types (
id integer,
type_id character varying,
explain character varying
);

And function:
CREATE or REPLACE FUNCTION hasType(_list character varying[]) RETURNS integer
   LANGUAGE plpgsql
   AS $$

DECLARE hasValue integer;
BEGIN
        EXECUTE 'SELECT 1 FROM types WHERE type_id ANY('|| _list ||') ' INTO hasValue;
        IF hasValue IS NULL THEN
                RETURN 0;
        ELSE
                RETURN 1;
        END IF;                                                
END;
$$;

Executing function with array parameter:
select hasType(ARRAY['E','F','','']);

I got error:
SQL error:
ERROR:  operator is not unique: unknown || character varying[] at character 49
HINT:  Could not choose a best candidate operator. You might need to add explicit type casts.
QUERY:  SELECT  'SELECT 1 FROM types WHERE type_id ANY('||  $1  ||') '
CONTEXT:  PL/pgSQL function "hastype" line 4 at EXECUTE statement
In statement:
select hasType(ARRAY['E','F','','']);

How to add array in parameter list to sql-sentence?

-kupen


--
Wippies-vallankumous on täällä! Varmista paikkasi vallankumouksen eturintamassa ja liity Wippiesiin heti!
http://www.wippies.com/




--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

pgsql-sql by date:

Previous
From: Pena Kupen
Date:
Subject: array in function
Next
From: Pena Kupen
Date:
Subject: Re: array in function