add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
create schema some_other_schema;
create extension intarray;
create extension postgis schema some_other_schema;
select extname,extnamespace::regnamespace from pg_extension;
CREATE SCHEMA

CREATE EXTENSION

CREATE EXTENSION
extname extnamespace
plpgsql pg_catalog
intarray public
postgis some_other_schema 
SELECT 3
select e.extnamespace::regnamespace
, e.extname
, p.pronamespace::regnamespace
, p.oid::regprocedure as "function with argument types"
from pg_catalog.pg_proc as p
join pg_catalog.pg_depend as d on (d.objid = p.oid)
join pg_catalog.pg_extension as e on (d.refobjid = e.oid)
where p.proname ilike 'uniq'
order by 2,4;
--these are in `intarray` extension which is in schema `public`
--and `public` is in `search_path`
show search_path;
select uniq(sort('{3,1,1,1,2}'::int[]));
extnamespace extname pronamespace function with argument types
public intarray public uniq(integer[]) 
SELECT 1
search_path
public, x_tablefunc, x_pg_trgm, x_intarray, x_hstore, x_tsm_system_rows, x_unaccent, x_ltree, x_postgis 
SHOW
uniq
{1,2,3} 
SELECT 1
--these are in `postgis` extension which is in schema `some_other_schema`
--and `some_other_schema` is NOT in `search_path`
select st_astext(st_geomfromtext('point(0 0)'));
ERROR:  function st_geomfromtext(unknown) does not exist
LINE 3: select st_astext(st_geomfromtext('point(0 0)'));
                         ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
select e.extnamespace::regnamespace
, e.extname
, p.pronamespace::regnamespace
, p.oid::regprocedure as "function with argument types"
from pg_catalog.pg_proc as p
join pg_catalog.pg_depend as d on (d.objid = p.oid)
join pg_catalog.pg_extension as e on (d.refobjid = e.oid)
where p.proname ilike any(array['st_geomfromtext','st_astext'])
order by 2,4;
--you need to either schema-qualify everything from the extension
select some_other_schema.st_astext('point(0 0)'::some_other_schema.geometry);
--or add that schema to search path
set search_path='some_other_schema';
select st_astext(st_geomfromtext('point(0 0)'));
extnamespace extname pronamespace function with argument types
some_other_schema postgis some_other_schema some_other_schema.st_astext(some_other_schema.geometry)
some_other_schema postgis some_other_schema some_other_schema.st_astext(some_other_schema.geometry,integer)
some_other_schema postgis some_other_schema some_other_schema.st_geomfromtext(text)
some_other_schema postgis some_other_schema some_other_schema.st_geomfromtext(text,integer)
some_other_schema postgis some_other_schema some_other_schema.st_astext(some_other_schema.geography)
some_other_schema postgis some_other_schema some_other_schema.st_astext(some_other_schema.geography,integer)
some_other_schema postgis some_other_schema some_other_schema.st_astext(text) 
SELECT 7
st_astext
POINT(0 0) 
SELECT 1

SET
st_astext
POINT(0 0) 
SELECT 1