Update
According to your sample data and desired result you can get it using json_object_agg Postgres function.
CREATE TABLE test (str text, num double precision);
INSERT INTO test(str, num) VALUES ('a', 1.0);
INSERT INTO test(str, num) VALUES ('b', 2.3);
select json_object_agg(str, num)
from test;
| json_object_agg |
| :--------------------- |
| { "a" : 1, "b" : 2.3 } |
dbfiddle here
You can use row_to_json Postgres function.
create table foo(f1 int, f2 varchar(10), f3 float, f4 decimal(18,2));
insert into foo values (2, 'name', 2.345, 23.23);
select row_to_json(f)
from (select f1,f2,f3,f4 from foo) f;
| row_to_json |
| :----------------------------------------- |
| {"f1":2,"f2":"name","f3":2.345,"f4":23.23} |
dbfiddle here
RDBMSs implement their own constructs. Then they add hundreds of function names, internal and user-facing.
All of them for PostgreSQL are defined in the schema pg_catalog
. You can access them with the S
argument in psql
. For example \dfS
for show all system functions.
On top of that, users go and define schema(ta)s, tables, indexes, custom functions, types, whatnot.
You can never know what users create. You'll have to check that out for each install by querying the catalog.
Is there a truly comprehensive way to list all these 'defined words' complete with their roles, origins, comments etc. in a single huge list?
I'm not 100% sure it's what you want, but perhaps you're looking for the Reserved Keyword list?
Update
"You'll have to check that out for each install by querying the catalog."—exactly, can you tell me such a query? – John Frazer Jun 17 at 8:03
Sure, so what you want to do is run psql -E
, then run \?
. This gives you the far more than what you've asked for (viz. list tables, views, and sequences , aggregates , access methods , tablespaces , conversions , casts , default privileges , domains , foreign tables , foreign servers , user mappings , foreign-data wrappers , functions , text search configurations , text search dictionaries , text search parsers , text search templates , roles , indexes , large objects, same as \lo_list , procedural languages , materialized views , schemas , operators , collations , table, view, and sequence access privileges , sequences , tables , data types , roles , views , foreign tables , extensions , event triggers , databases). Next pick one of them, like \df
which lists functions,
You'll get a query like
SELECT n.nspname as "Schema",
p.proname as "Name",
pg_catalog.pg_get_function_result(p.oid) as "Result data type",
pg_catalog.pg_get_function_arguments(p.oid) as "Argument data types",
CASE
WHEN p.proisagg THEN 'agg'
WHEN p.proiswindow THEN 'window'
WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN 'trigger'
ELSE 'normal'
END as "Type"
FROM pg_catalog.pg_proc p
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE pg_catalog.pg_function_is_visible(p.oid)
AND n.nspname <> 'pg_catalog'
AND n.nspname <> 'information_schema'
ORDER BY 1, 2, 4;
This is internally the query that psql uses to get this information. You can cut it up though if you only want the name and don't care about whether or not the function is an aggregate, window, or trigger; and, you can cut it further if you don't care about the result and arguments types.
SELECT n.nspname as "Schema",
p.proname as "Name"
FROM pg_catalog.pg_proc p
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE pg_catalog.pg_function_is_visible(p.oid)
AND n.nspname <> 'pg_catalog'
AND n.nspname <> 'information_schema'
ORDER BY 1, 2;
Etc., or of course you can query the slower information_schema
interface which is part of the SQL spec and standardized.
SELECT routine_catalog, routine_schema, routine_name
FROM information_schema.routines
WHERE routine_schema NOT IN ('pg_catalog', 'information_schema');
Best Answer
You can get the definition of a function from the
pg_proc
system catalog.If
prorettype
is contains the specialrecord
type, the actual return types are the elements inproallargtypes
where the correspondingproargmodes
entry ist
,o
orb
.This query should give you all functions maned
test
together with their object ID, the input types and the result types: