PostgreSQL function with duplicate parameters

Question

I came across a curious function signature in pg_catalog.pg_stat_get_activity :

 CREATE OR REPLACE FUNCTION pg_stat_get_activity( IN pid integer, OUT datid oid, OUT pid integer, -- more parameters...) RETURNS SETOF record AS 'pg_stat_get_activity' LANGUAGE internal STABLE COST 1 ROWS 100; 

This function twice declares the same parameter name, which is also reported in information_schema .

 select parameter_mode, parameter_name from information_schema.parameters where specific_schema = 'pg_catalog' and specific_name like 'pg_stat_get_activity%' order by ordinal_position 

The above results (see also SQLFiddle ):

 +--------------+----------------+ |parameter_mode|parameter_name | +--------------+----------------+ |IN |pid | |OUT |datid | |OUT |pid | |... |... | +--------------+----------------+ 

Naively, I tried to create a similar function to no avail:

 CREATE FUNCTION f_2647(p1 IN int, p1 OUT int) AS $$ BEGIN p1 := p1; END; $$ LANGUAGE plpgsql; 

My questions:

• Why pg_stat_get_activity internal pg_stat_get_activity function repeat the same parameter name twice? What is the purpose of this? For instance. why not just use the INOUT parameter?
• What is the difference between the internal function pg_stat_get_activity and mine? Why can't I use this syntax?

I know these are more academic questions, but I need to get this right to fix the issue in jOOQ codegenerator.

Answer 1

I notice that he appeared in 9.2. In version 9.1, the out field was named procpid :

  parameter_mode |  parameter_name  
 ---------------- + ------------------
  IN |  pid
  OUT |  datid
  OUT |  procpid
  OUT |  usesysid
  ...

Finding changes in the postgres git history leads to this commit:

  commit 4f42b546fd87a80be30c53a0f2c897acb826ad52
 Author: Magnus Hagander 
 Date: Thu Jan 19 14:19:20 2012 +0100

     Separate state from query string in pg_stat_activity

     This separates the state (running / idle / idleintransaction etc) into
     it own field ("state"), and leaves the query field containing just
     query text.

     The query text will now mean "current query" when a query is running
     and "last query" in other states.  Accordingly, the field has been
     renamed from current_query to query.

     Since backwards compatibility was broken anyway to make that, the procpid
     field has also been renamed to pid - along with the same field in
     pg_stat_replication for consistency.

     Scott Mead and Magnus Hagander, review work from Greg Smith

Among the lines that have changed, here is the interest:

  -DATA (insert OID = 2022 (pg_stat_get_activity PGNSP PGUID 12 1 100 0 0 ffffts 1 0 2249 "23" "{23,26,23,26,25,25,16,1184,1184,1184,869,25,23 } "" {i, o, o, o, o, o, o, o, o, o, o, o, o} "" {pid, datid, procpid, usesysid, application_name, current_query, waiting, xact_start, query_start , backend_start, client_addr, client_hostname, client_port} "_null_ pg_stat_get_activity _null_ _null_ _null_));
 + DATA (insert OID = 2022 (pg_stat_get_activity PGNSP PGUID 12 1 100 0 0 ffffts 1 0 2249 "23" "{23,26,23,26,25,25,25,16,1184,1184,1184,1184,869 , 25,23} "" {i, o, o, o, o, o, o, o, o, o, o, o, o, o, o} "" {pid, datid, pid, usesysid, application_name , state, query, waiting, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port} "_null_ pg_stat_get_activity _null_ _null_ _null_));
 

In this previously digested form, it was plausible that the authors did not notice the double use of pid , otherwise they didn’t care, since it was harmless in practice.

This is skipped because these internal functions are created using initdb in a fast path that skips checking for regular user-defined functions.