agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: Pena Kupen <kupen@wippies.fi>
To: pgsql-sql@postgresql.org
Subject: array in function
Date: Mon, 24 Feb 2014 10:42:20 +0200 (EET)
Message-ID: <1750529416.3096311393231340773.JavaMail.kupen@wippies.fi> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>

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

view thread (5+ messages)  latest in thread

Message-ID: <1750529416.3096311393231340773.JavaMail.kupen@wippies.fi>
Permalink:  ../1750529416.3096311393231340773.JavaMail.kupen@wippies.fi/
Also on:    postgresql.org/message-id/1750529416.3096311393231340773.JavaMail.kupen@wippies.fi

reply

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: kupen@wippies.fi
  Subject: Re: array in function
  In-Reply-To: <1750529416.3096311393231340773.JavaMail.kupen@wippies.fi>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox