CODEWITHSUNDEEP
postgresqlstored-proceduresplpgsql
Neel Basu
Neel Basu

Reputation: 12904

plpgsql associative array argumnent

I once read this entry in mailing list http://archives.postgresql.org/pgsql-hackers/2005-06/msg01481.php

SELECT *
FROM foo_func(
    c => current_timestamp::timestamp with time zone,
    a => 2,
    b => 5
);

Now I need this kindof solution where I can pass associative array argument to a function. Do I need to make a dummy table and then use that table as argument type ? or there is any straight forward fix for this ? or has this hack been implemented ?

or can I emulate the same using pl/python ?

Upvotes: 0

Views: 3373

Answers (2)

Daniel Vérité
Daniel Vérité

Reputation: 61506

Here are the steps for an answer with hstore and PG-8.4 for debian.

1) if not installed already, install the contrib package

# apt-get install postgresql-contrib-8.4

2) install hstore in the relevant database

$ psql -U postgres -d dbname
# \i /usr/share/postgresql/8.4/contrib/hstore.sql

2bis) If the plpgsql language is not installed, install it (still inside psql as postgres user)

# CREATE LANGUAGE plpgsql;

3) create the function taking hstore as input. Here's an example in plpgsql that just enumerates the keys and values:

CREATE OR REPLACE function enum_hstore(in_h hstore) returns void
as $$
declare
  kv record;
begin
  for kv in select * from (select (each(in_h)).*) as f(k,v) loop
    raise notice 'key=%,value=%',kv.k,kv.v;
  end loop;
end
$$ language plpgsql;

4) call the function. Since the keys and values are of type text, it may be necessary to cast to text the non-literal entries, as the current_timestamp call in the question. Example:

  select enum_hstore(
    hstore('c',current_timestamp::text) ||
    'a=>2,b=>5'::hstore
  );

The result to expect from the above function:

 NOTICE:  key=a,value=2
 NOTICE:  key=b,value=5
 NOTICE:  key=c,value=2012-04-08 16:12:59.410056+02

Upvotes: 2

Frank Heikens
Frank Heikens

Reputation: 126970

This is implemented in version 9.0:

4.3.2. Using named notation

In named notation, each argument's name is specified using := to separate it from the argument expression. For example:

SELECT concat_lower_or_upper(a := 'Hello', b := 'World');
 concat_lower_or_upper 
-----------------------
 hello world
(1 row)

Upvotes: 2

Related Questions