PostgreSQL – Can Current search_path Be Used in Query?

The %TYPE construct can only copy the exact type. But there is a simple workaround to register the according array type: Create a (temporary) table or view with the desired array type.

Template table:

CREATE TABLE foo (i int);

(Temporary) view to register the array type, optionally empty (LIMIT 0):

CREATE TEMP VIEW v_foo_i_arr AS
SELECT ARRAY (SELECT i FROM foo LIMIT 0) AS i_arr;

Or a (temporary) table:

CREATE TEMP TABLE foo_i_arr AS
SELECT ARRAY (SELECT i FROM foo LIMIT 0) AS i_arr;

Now your function works:

CREATE OR REPLACE FUNCTION f_arr_test(v_foo_i_arr.i_arr%TYPE)
  RETURNS foo.i%TYPE AS  -- example: return element type
$func$
BEGIN
   RETURN $1[2];
END
$func$  LANGUAGE plpgsql;

You might as well make that a temporary view or table, since the type in the function signature is converted to the underlying type immediately. No persisted connection to the used template. So it's no problem that temp objects are dropped at the end of the session.

Polymorphic type

You might be better off with a polymorphic type to begin with. This works for any array type:

CREATE OR REPLACE FUNCTION f_arr_test_polymorphic(ANYARRAY)
  RETURNS ANYELEMENT AS  -- derived from input type
$func$
BEGIN
   RETURN $1[2];
END
$func$  LANGUAGE plpgsql;

SQL Fiddle.

Note that using a %TYPE declaration inside the function body actually works dynamically in that it looks up the type in the system catalog at the first call of every session.

Related question on SO:

• PL/pgSQL - %TYPE and ARRAY

Factory defaults

How can I reset it back to whatever the default that a pristine, unaltered database on this cluster would have?

You can read boot_val from pg_settings, but that's actually the compiled-in factory default, not the setting in postgresql.conf.

SELECT boot_val
FROM   pg_settings
WHERE  name LIKE 'search_path';

You aren't overlooking the simple RESET?

To just reset the setting (remove it), you can simply:

ALTER DATABASE my_db RESET search_path;

But that doesn't necessarily give you what "a pristine, unaltered database on this cluster would have". It just removes the custom setting for the database. There are several ways to set the search path:

• How does the search_path influence identifier resolution and the “current schema”

And it does not change the search_path of your current session. The effect (if any) is visible in your next session.

Related:

• What is the search_path for a given database and user?

Connect to template1

You could get the current setting from a connection to template1, like @Abelisto suggested. But you probably want to stay within your current session. You could use dblink for that:

SELECT * FROM dblink('hostaddr=127.0.0.1 dbname=template1 user=postgres password=you_wish'
                         , $$SELECT boot_val, source FROM pg_settings
                             WHERE name LIKE 'search_path'$$
                          ) AS t(search_path text, source text);

search_path    | source
---------------+---------
"$user",public | default

This has a couple of flaws:

• You need the additional module dblink installed. That's simple:

  • Persistent inserts in a UDF even if the function aborts

• You need the privilege to connect to template1. And you need to supply the user pw.

• Even in template1 the setting might stem from settings to the role or database (or another instance) as well (even though they probably don't). To make sure check the columns source. If it says 'default', you got it. If it says 'database' or 'user' you are back to square 1 ...

Read actual setting in postgresql.conf

.. usingpg_read_file(), but this is also subject to some important restrictions. The manual:

Use of these functions is restricted to superusers.

And:

Only files within the database cluster directory and the log_directory can be accessed.

Bold emphasis mine.
If your config files are somewhere else (like in default installations on Debian and friends) you would have to create a symbolic link in the file system in your data or log directory to your actual config file.

SELECT substring(txt, $$\nsearch_path\s*=\s*'([^']+)'$$) AS search_path
FROM   pg_read_file((SELECT setting FROM pg_settings WHERE name = 'config_file')
                  , 0, 10000000) AS txt;  -- arbitrary 10 MB max.

search_path
------------
public

Note how I fetch the path to the config file dynamically with:

SELECT setting FROM pg_settings WHERE name = 'config_file'

Then I use a regular expression with substring() to extract the setting:

\nsearch_path\s*=\s*'([^']+)'

That should work, I didn't spend much time to make the regexp bullet-proof, though. Might be fooled by a commented setting that matches first or something. You might want to test some more.

Finally, since Postgres 9.4, there is also the ALTER SYSTEM command to override settings in a postgresql.auto.conf file. You'll have to check that, too.