How to pass custom type array to Postgres function

by

You can use the alternative syntax with a array literal instead of the array constructor, which is a Postgres function-like construct and may cause trouble when you need to pass values – like in a prepared statement:

SELECT myschema.myfunc('0d6311cc-0d74-4a32-8cf9-87835651e1ee'
                  , '{"(0d6311cc-0d74-4a32-8cf9-87835651e1ee, 25)"
                    , "(6449fb3b-844e-440e-8973-31eb6bbefc81, 10)"}'::mytype[]);

I added a line break between the two row types in the array for display. That’s legal.

How to find the correct syntax for any literal?

Just ask Postgres. Here is a demo:

CREATE TABLE mytype (id uuid, amount numeric(13,4));

INSERT INTO mytype VALUES
  ('0d6311cc-0d74-4a32-8cf9-87835651e1ee', 25)
 ,('6449fb3b-844e-440e-8973-31eb6bbefc81', 10);

SELECT ARRAY(SELECT m FROM mytype m);

Returns:

{"(0d6311cc-0d74-4a32-8cf9-87835651e1ee,25.0000)","(6449fb3b-844e-440e-8973-31eb6bbefc81,10.0000)"}

db<>fiddle here

Any table (including temporary tables) implicitly creates a row type of the same name.

More Related Contents:

  1. Cannot simply use PostgreSQL table name (“relation does not exist”)
  2. Best way to get result count before LIMIT was applied
  3. PL/pgSQL checking if a row exists
  4. Postgres function returning table not returning data in columns
  5. Pass array literal to PostgreSQL function
  6. How to perform a select query in a DO block?
  7. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘WHERE `id` [closed]
  8. how to get client’s IP address in sql database [closed]
  9. Combine multiple sql / mysql queries in one query
  10. Cleansing User Passwords
  11. Commands out of sync; you can’t run this command now
  12. SELECT COUNT(*) AS count – How to use this count
  13. How to return result of a SELECT inside a function in PostgreSQL?
  14. selecting unique values from a column
  15. Simple Postgresql Statement – column name does not exists
  16. Simple PHP Pagination script [closed]
  17. Use bound parameter multiple times
  18. Pass multiple values in single parameter
  19. GROUP BY and aggregate sequential numeric values
  20. Create a dynamic mysql query using php variables
  21. How to separate DATE and TIME from DATETIME in MySQL?
  22. Setting a connect timeout with PDO
  23. Fatal error: Uncaught exception ‘mysqli_sql_exception’ with message ‘No index used in query/prepared statement’
  24. How to generate unique id in MySQL?
  25. Check if NULL exists in Postgres array
  26. I’m getting this error code: Invalid argument supplied for foreach() [duplicate]
  27. How to use SQL LIKE condition with multiple values in PostgreSQL?
  28. Do I have to use mysql_real_escape_string if I bind parameters?
  29. PDO and MySQL ‘between’
  30. MySQL Join Multiple Rows as Columns

Leave a Comment