CODEWITHSUNDEEP
postgresql
Dnyaneshwar Jadhav
Dnyaneshwar Jadhav

Reputation: 353

Postgres function input parameter as TEXT Array problem

My Table looks like

CREATE TABLE dev.clbk_logs
(
    id bigint NOT NULL,
    clbk_typ character varying(255) COLLATE pg_catalog."default",
    clbk_json json,
    cre_dte timestamp without time zone,
    ld_id bigint,
    ld_num character varying(255) COLLATE pg_catalog."default",
    mod_dte timestamp without time zone,
    CONSTRAINT clbk_logs_pkey PRIMARY KEY (id)
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

My function is

CREATE OR REPLACE FUNCTION dev.my_method(p_callback_types TEXT[], p_days_ago INT) 
RETURNS SETOF dev.clbk_logs 
    LANGUAGE 'plpgsql'
AS $BODY$      
BEGIN
    return query
    SELECT * FROM dev.clbk_logs  
    WHERE  (clbk_Typ::TEXT) IN (($1))  AND (current_date - cre_dte::date)< p_days_ago;
       
END;
$BODY

Can someone please help what is wrong in above, and should make expected result. I wanted to pass an array of string and in query.

Error I am getting is

LINE 2:  WHERE  (clbk_Typ::TEXT) IN (($1))  AND (current_date - cre_...
                                 ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.
QUERY:  SELECT * FROM dev.clbk_logs  
    WHERE  (clbk_Typ::TEXT) IN (($1))  AND (current_date - cre_dte::date)< p_days_ago
CONTEXT:  PL/pgSQL function dev.my_method(text[],integer) line 3 at RETURN QUERY
SQL state: 42883

Upvotes: 0

Views: 380

Answers (1)

user330315
user330315

Reputation:

You need to use = any() not IN with an array.

The cast to ::text is also not needed. And for readability I would recommend using the parameter name, rather than the number:

CREATE OR REPLACE FUNCTION dev.my_method(p_callback_types TEXT[], p_days_ago INT) 
RETURNS SETOF dev.fourkites_clbk_logs 
    LANGUAGE plpgsql
AS $BODY$      
BEGIN
    return query
    SELECT * 
    FROM dev.fourkites_clbk_logs  
    WHERE clbk_Typ = any (p_callback_type)  
      AND (current_date - cre_dte::date) < p_days_ago;
END;
$BODY

Note that your condition on cre_dte can't use an index if you ever create one. If you want that condition to be able to use an index, change it to:

and cre_dte >= current_date - p_days_ago;

Upvotes: 1

Related Questions