##

# libsys.sql - SQL to support driver features

##

-- Queries for dealing with the PostgreSQL catalogs for supporting the driver.

[lookup_type::first]

SELECT

ns.nspname as namespace,

bt.typname,

bt.typtype,

bt.typlen,

bt.typelem,

bt.typrelid,

ae.oid AS ae_typid,

ae.typreceive::oid != 0 AS ae_hasbin_input,

ae.typsend::oid != 0 AS ae_hasbin_output

FROM pg_catalog.pg_type bt

LEFT JOIN pg_type ae

ON (

bt.typlen = -1 AND

bt.typelem != 0 AND

bt.typelem = ae.oid

)

LEFT JOIN pg_catalog.pg_namespace ns

ON (ns.oid = bt.typnamespace)

WHERE bt.oid = $1

[lookup_composite]

-- Get the type Oid and name of the attributes in `attnum` order.

SELECT

CAST(atttypid AS oid) AS atttypid,

CAST(attname AS text) AS attname

FROM

pg_catalog.pg_type t LEFT JOIN pg_catalog.pg_attribute a

ON (t.typrelid = a.attrelid)

WHERE

attrelid = $1 AND NOT attisdropped AND attnum > 0

ORDER BY attnum ASC

[lookup_procedures]

SELECT

pg_proc.oid,

pg_proc.*,

pg_proc.oid::regproc AS _proid,

pg_proc.oid::regprocedure as procedure_id,

COALESCE(string_to_array(trim(replace(textin(oidvectorout(proargtypes)), ',', ' '), '{}'), ' ')::oid[], '{}'::oid[])

AS proargtypes,

(pg_type.oid = 'record'::regtype or pg_type.typtype = 'c') AS composite

FROM

pg_catalog.pg_proc LEFT JOIN pg_catalog.pg_type ON (

pg_proc.prorettype = pg_type.oid

)

[lookup_procedure_oid::first]

*[lookup_procedures]

WHERE pg_proc.oid = $1

[lookup_procedure_rp::first]

*[lookup_procedures]

WHERE pg_proc.oid = regprocedurein($1)

[lookup_prepared_xacts::first]

SELECT

COALESCE(ARRAY(

SELECT

gid::text

FROM

pg_catalog.pg_prepared_xacts

WHERE

database = current_database()

AND (

owner = $1::text

OR (

(SELECT rolsuper FROM pg_roles WHERE rolname = $1::text)

)

)

ORDER BY prepared ASC

), ('{}'::text[]))

[regtypes::column]

SELECT pg_catalog.regtypein(pg_catalog.textout(($1::text[])[i]))::oid AS typoid

FROM pg_catalog.generate_series(1, array_upper($1::text[], 1)) AS g(i)

[xact_is_prepared::first]

SELECT TRUE FROM pg_catalog.pg_prepared_xacts WHERE gid::text = $1

[get_statement_source::first]

SELECT statement FROM pg_catalog.pg_prepared_statements WHERE name = $1

[setting_get]

SELECT setting FROM pg_catalog.pg_settings WHERE name = $1

[setting_set::first]

SELECT pg_catalog.set_config($1, $2, false)

[setting_len::first]

SELECT count(*) FROM pg_catalog.pg_settings

[setting_item]

SELECT name, setting FROM pg_catalog.pg_settings WHERE name = $1

[setting_mget]

SELECT name, setting FROM pg_catalog.pg_settings WHERE name = ANY ($1)

[setting_keys]

SELECT name FROM pg_catalog.pg_settings ORDER BY name

[setting_values]

SELECT setting FROM pg_catalog.pg_settings ORDER BY name

[setting_items]

SELECT name, setting FROM pg_catalog.pg_settings ORDER BY name

[setting_update]

SELECT

($1::text[][])[i][1] AS key,

pg_catalog.set_config(($1::text[][])[i][1], $1[i][2], false) AS value

FROM

pg_catalog.generate_series(1, array_upper(($1::text[][]), 1)) g(i)

[startup_data:transient:first]

-- 8.2 and greater

SELECT

pg_catalog.version()::text AS version,

backend_start::text,

client_addr::text,

client_port::int

FROM pg_catalog.pg_stat_activity WHERE procpid = pg_catalog.pg_backend_pid()

UNION ALL SELECT

pg_catalog.version()::text AS version,

NULL::text AS backend_start,

NULL::text AS client_addr,

NULL::int AS client_port

LIMIT 1;

[startup_data_no_start:transient:first]

-- 8.1 only, but is unused as often the backend's activity row is not

-- immediately present.

SELECT

pg_catalog.version()::text AS version,

NULL::text AS backend_start,

client_addr::text,

client_port::int

FROM pg_catalog.pg_stat_activity WHERE procpid = pg_catalog.pg_backend_pid();

[startup_data_only_version:transient:first]

-- In 8.0, there's nothing there.

SELECT

pg_catalog.version()::text AS version,

NULL::text AS backend_start,

NULL::text AS client_addr,

NULL::int AS client_port;

[terminate_backends:transient:column]

-- Terminate all except mine.

SELECT

procpid, pg_catalog.pg_terminate_backend(procpid)

FROM

pg_catalog.pg_stat_activity

WHERE

procpid != pg_catalog.pg_backend_pid()

[cancel_backends:transient:column]

-- Cancel all except mine.

SELECT

procpid, pg_catalog.pg_cancel_backend(procpid)

FROM

pg_catalog.pg_stat_activity

WHERE

procpid != pg_catalog.pg_backend_pid()

[sizeof_db:transient:first]

SELECT pg_catalog.pg_database_size(current_database())::bigint

[sizeof_cluster:transient:first]

SELECT SUM(pg_catalog.pg_database_size(datname))::bigint FROM pg_database

[sizeof_relation::first]

SELECT pg_catalog.pg_relation_size($1::text)::bigint

[pg_reload_conf:transient:]

SELECT pg_reload_conf()

[languages:transient:column]

SELECT lanname FROM pg_catalog.pg_language

[listening_channels:transient:column]

SELECT channel FROM pg_catalog.pg_listening_channels() AS x(channel)

[listening_relations:transient:column]

-- listening_relations: old version of listening_channels.

SELECT relname as channel FROM pg_catalog.pg_listener

WHERE listenerpid = pg_catalog.pg_backend_pid();

[notify::first]

-- 9.0 and greater

SELECT

COUNT(pg_catalog.pg_notify(($1::text[])[i][1], $1[i][2]) IS NULL)

FROM

pg_catalog.generate_series(1, array_upper($1, 1)) AS g(i)

[release_advisory_shared]

SELECT

CASE WHEN ($2::int8[])[i] IS NULL

THEN

pg_catalog.pg_advisory_unlock_shared(($1::int4[])[i][1], $1[i][2])

ELSE

pg_catalog.pg_advisory_unlock_shared($2[i])

END AS released

FROM

pg_catalog.generate_series(1, COALESCE(array_upper($2::int8[], 1), array_upper($1::int4[], 1))) AS g(i)

[acquire_advisory_shared]

SELECT COUNT((

CASE WHEN ($2::int8[])[i] IS NULL

THEN

pg_catalog.pg_advisory_lock_shared(($1::int4[])[i][1], $1[i][2])

ELSE

pg_catalog.pg_advisory_lock_shared($2[i])

END

) IS NULL) AS acquired

FROM

pg_catalog.generate_series(1, COALESCE(array_upper($2::int8[], 1), array_upper($1::int4[], 1))) AS g(i)

[try_advisory_shared]

SELECT

CASE WHEN ($2::int8[])[i] IS NULL

THEN

pg_catalog.pg_try_advisory_lock_shared(($1::int4[])[i][1], $1[i][2])

ELSE

pg_catalog.pg_try_advisory_lock_shared($2[i])

END AS acquired

FROM

pg_catalog.generate_series(1, COALESCE(array_upper($2::int8[], 1), array_upper($1::int4[], 1))) AS g(i)

[release_advisory_exclusive]

SELECT

CASE WHEN ($2::int8[])[i] IS NULL

THEN

pg_catalog.pg_advisory_unlock(($1::int4[])[i][1], $1[i][2])

ELSE

pg_catalog.pg_advisory_unlock($2[i])

END AS released

FROM

pg_catalog.generate_series(1, COALESCE(array_upper($2::int8[], 1), array_upper($1::int4[], 1))) AS g(i)

[acquire_advisory_exclusive]

SELECT COUNT((

CASE WHEN ($2::int8[])[i] IS NULL

THEN

pg_catalog.pg_advisory_lock(($1::int4[])[i][1], $1[i][2])

ELSE

pg_catalog.pg_advisory_lock($2[i])

END

) IS NULL) AS acquired -- Guaranteed to be acquired once complete.

FROM

pg_catalog.generate_series(1, COALESCE(array_upper($2::int8[], 1), array_upper($1::int4[], 1))) AS g(i)

[try_advisory_exclusive]

SELECT

CASE WHEN ($2::int8[])[i] IS NULL

THEN

pg_catalog.pg_try_advisory_lock(($1::int4[])[i][1], $1[i][2])

ELSE

pg_catalog.pg_try_advisory_lock($2[i])

END AS acquired

FROM

pg_catalog.generate_series(1, COALESCE(array_upper($2::int8[], 1), array_upper($1::int4[], 1))) AS g(i)