list functions returns error

[pashagolub]

$ go install github.com/xo/usql@master
go: downloading github.com/xo/usql v0.13.2-0.20221206074426-030c5c6a58f5
...

$ usql postgresql://pasha@127.0.0.1/timetable?sslmode=disable
...
pg:pasha@127/timetable=> \df
error: failed to list functions: sql: Scan error on column index 5, name "data_type": converting NULL to string is unsupported
pg:pasha@127/timetable=> \conninfo
Connected with driver postgres (dbname=timetable host=127.0.0.1 sslmode=disable user=pasha)

[nineinchnick]

Do you have any functions that return custom data types? I checked functions that return void and it works, so I need something else to be able to reproduce this.

[pashagolub]

I'm using this schema https://github.com/cybertec-postgresql/pg_timetable/tree/master/internal/pgengine/sql

Let me know if I can help

[nineinchnick]

I imported this schema into PostgreSQL 15 running in a container and I was able to list all functions with \df.

Can you execute these queries and include the results here? One of them returns nulls for the data_type column.

SELECT
  specific_name,
  routine_catalog,
  routine_schema,
  routine_name,
  COALESCE(routine_type, ''),
  data_type,
  routine_definition,
  COALESCE(external_language, routine_body) AS language,
  is_deterministic,
  security_type
FROM information_schema.routines
WHERE data_type IS NULL
ORDER BY 2, 3, 4

and

SELECT
  specific_catalog,
  specific_schema,
  specific_name,
  COALESCE(parameter_name, ''),
  ordinal_position,
  COALESCE(parameter_mode, ''),
  data_type,
  COALESCE(character_maximum_length, numeric_precision, datetime_precision, interval_precision, 0),
  COALESCE(numeric_scale, 0),
  COALESCE(numeric_precision_radix, 10),
  COALESCE(character_octet_length, 0) FROM information_schema.parameters

WHERE data_type IS NULL
ORDER BY 1, 2, 3, 5

I got these after running \set ECHO_HIDDEN on and modified them to find these nulls in all schemas you have.

[nineinchnick]

I'll probably change these queries to use COALESCE(data_type, '') like we do for routine_type but I'm curious when functions can have nulls there. And I'd like to be able to write a test for this 😄

[pashagolub]

Sure and that's no surprise. the data_type is null for procedures and for functions with OUT parameters, AFAIR

timetable=# SELECT
timetable-#   specific_name,
timetable-#   routine_catalog,
timetable-#   routine_schema,
timetable-#   routine_name,
timetable-#   COALESCE(routine_type, ''),
timetable-#   data_type,
timetable-#   routine_definition,
timetable-#   COALESCE(external_language, routine_body) AS language,
timetable-#   is_deterministic,
timetable-#   security_type
timetable-# FROM information_schema.routines
timetable-# WHERE data_type IS NULL
timetable-# ORDER BY 2, 3, 4;
  specific_name   | routine_catalog | routine_schema | routine_name | coalesce  | data_type |    routine_definition     | language | is_deterministic | security_type
------------------+-----------------+----------------+--------------+-----------+-----------+---------------------------+----------+------------------+---------------
 test_proc_659922 | timetable       | public         | test_proc    | PROCEDURE |           | \r                       +| PLPGSQL  | NO               | INVOKER
                  |                 |                |              |           |           | BEGIN\r                  +|          |                  |
                  |                 |                |              |           |           |     PERFORM f('hey 1');\r+|          |                  |
                  |                 |                |              |           |           |     COMMIT;\r            +|          |                  |
                  |                 |                |              |           |           |     PERFORM f('hey 2');\r+|          |                  |
                  |                 |                |              |           |           |     COMMIT;\r            +|          |                  |
                  |                 |                |              |           |           | END;\r                   +|          |                  |
                  |                 |                |              |           |           |                           |          |                  |
(1 row)

timetable=# SELECT
timetable-#   specific_catalog,
timetable-#   specific_schema,
timetable-#   specific_name,
timetable-#   COALESCE(parameter_name, ''),
timetable-#   ordinal_position,
timetable-#   COALESCE(parameter_mode, ''),
timetable-#   data_type,
timetable-#   COALESCE(character_maximum_length, numeric_precision, datetime_precision, interval_precision, 0),
timetable-#   COALESCE(numeric_scale, 0),
timetable-#   COALESCE(numeric_precision_radix, 10),
timetable-#   COALESCE(character_octet_length, 0) FROM information_schema.parameters
timetable-#
timetable-# WHERE data_type IS NULL
timetable-# ORDER BY 1, 2, 3, 5;
 specific_catalog | specific_schema | specific_name | coalesce | ordinal_position | coalesce | data_type | coalesce | coalesce | coalesce | coalesce
------------------+-----------------+---------------+----------+------------------+----------+-----------+----------+----------+----------+----------
(0 rows)

[pashagolub]

And yes, this procedure is not included in the schema I provided, sorry. My bad

[nineinchnick]

I just pushed https://github.com/xo/usql/commit/8a991960887217180188eb93ceb75f568e03a97f, can you give it a try? We missed that because we test all the metadata reader functions in PostgreSQL using this schema, which doesn't have procedures: https://github.com/jOOQ/sakila/blob/main/postgres-sakila-db/postgres-sakila-schema.sql

[pashagolub]

Workds like a charm