PL/pgSQL function syntax with integer arrays

Question

I'm trying to get a SQL Function started so that I can gather a list of part ids that I don't want in another query. I've tried using PGAdmin's Function builder to get started but I have a syntax error so it won't create the function. Can someone tell me what I am doing wrong here? Once I can get the function started, I think I can figure out the rest. Just need to figure out the proper declaration syntax (I'm more used to MSSQL's Transaction SQL syntax)

Here is my code so far:

CREATE FUNCTION get_unused_part_ids() RETURNS integer[] AS
$BODY$DECLARE
  part_ids integer ARRAY;

BEGIN
  set part_ids = '{1,2,3,4}'
  select part_ids

END;$BODY$
LANGUAGE sql VOLATILE;

What I'm expecting is an array of integers with the values 1, 2, 3 and 4.

klin · Accepted Answer · 2015-10-21 22:34:35Z

1

Example function with proper syntax:

CREATE FUNCTION get_unused_part_ids() RETURNS integer[] AS
$BODY$
DECLARE
    part_ids integer ARRAY;

BEGIN
    part_ids = '{1,2,3,4}';
    return part_ids;
END;
$BODY$ LANGUAGE plpgsql;

select * from get_unused_part_ids();

 get_unused_part_ids 
---------------------
 {1,2,3,4}
(1 row)

Note, you need plpgsql function to have local variables.

answered Oct 21, 2015 at 22:34

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user330315 Over a year ago

I prefer array[1,2,3,4] over the string notation

Pavel Stehule · Accepted Answer · 2015-10-22 05:54:38Z

The reply from @klin is correct, I have one advice. Use SQL language instead. In described case PostgreSQL planner can use better plan. PLpgSQL function is blackbox for planner:

CREATE OR REPLACE FUNCTION public.foo1()
 RETURNS integer[]
 LANGUAGE plpgsql
AS $function$
BEGIN
  RETURN ARRAY[1,2,3,4];
END;
$function$

CREATE OR REPLACE FUNCTION public.foo2()
 RETURNS integer[]
 LANGUAGE sql
AS $function$
  SELECT ARRAY[1,2,3,4];
$function$

CREATE TABLE boo(a int);
INSERT INTO boo SELECT random()*10 FROM generate_series(1,10000);
ANALYZE boo;

postgres=# EXPLAIN ANALYZE SELECT * FROM boo WHERE a = ANY(foo1());
                                             QUERY PLAN                                               
═════════════════════════════════════════════════════════════════════════════════════════════════════
Seq Scan on boo  (cost=0.00..2770.00 rows=6145 width=4) (actual time=0.118..49.949 rows=4104 loops=1)
  Filter: (a = ANY (foo1()))
  Rows Removed by Filter: 5896
Planning time: 0.096 ms
Execution time: 50.900 ms
(5 rows)

Time: 51.771 ms
postgres=# EXPLAIN ANALYZE SELECT * FROM boo WHERE a = ANY(foo2());
                                            QUERY PLAN                                              
═══════════════════════════════════════════════════════════════════════════════════════════════════
Seq Scan on boo  (cost=0.00..195.00 rows=4104 width=4) (actual time=0.036..4.700 rows=4104 loops=1)
  Filter: (a = ANY ('{1,2,3,4}'::integer[]))
  Rows Removed by Filter: 5896
Planning time: 0.193 ms
Execution time: 5.254 ms
(5 rows)

With SQL function is little bit better result estimation. The SQL function is a macro - and if you use SQL function, then it is equivalent of directly written query:

postgres=# EXPLAIN ANALYZE SELECT * FROM boo WHERE a = ANY(ARRAY[1,2,3,4]);
                                                QUERY PLAN                                              
═══════════════════════════════════════════════════════════════════════════════════════════════════
Seq Scan on boo  (cost=0.00..195.00 rows=4104 width=4) (actual time=0.032..4.782 rows=4104 loops=1)
  Filter: (a = ANY ('{1,2,3,4}'::integer[]))
  Rows Removed by Filter: 5896
Planning time: 0.122 ms
Execution time: 5.325 ms
(5 rows)

In this case the optimalizer has more informations and can to use it. For functions with constant result, the SQL functions are usually better.

Collectives™ on Stack Overflow

PL/pgSQL function syntax with integer arrays

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related