Timeline for How do you run "SELECT" on the composite type argument of an SQL function?
Current License: CC BY-SA 4.0
11 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Sep 30, 2022 at 14:28 | comment | added | user234725 | An array of a composite type is still a 1-D array. If you want to go into detail on usage, ask a new question. Comments are not for extended discussion. | |
| Sep 30, 2022 at 9:22 | comment | added | Biller Builder |
How do you use the array of composites though? unnest() assumes a multidimensional array for its multicolumn syntax.
|
|
| Sep 30, 2022 at 5:35 | vote | accept | Biller Builder | ||
| Sep 30, 2022 at 4:49 | comment | added | user234725 | I added some additional information to the answer. | |
| Sep 30, 2022 at 3:58 | history | edited | user234725 | CC BY-SA 4.0 |
Overhaul answer
|
| Sep 29, 2022 at 15:58 | comment | added | Biller Builder |
Either copy or write your own which would add entities and then pass the IDs of newly created entities as an array to the get_entities() instead of just RETURNING. This will show how to pass the array argument from the set of records and that functions are composable.
|
|
| Sep 29, 2022 at 14:21 | comment | added | user234725 | Sorry, I don’t understand what you want me to do… you want me to copy your create_entities() function from your fiddle into my answer? Your fiddle looks correct. | |
| Sep 29, 2022 at 9:49 | comment | added | Biller Builder |
Okay, turns out it's not possible to have a table/recordset as an argument to a function. So add a create_entities() function your answer which invokes get_entities() on the final set (to show how it is supposed to work within queries) and I'll accept your answer. For the reference see dbfiddle.uk/nBffoDMj
|
|
| Sep 28, 2022 at 11:55 | comment | added | Biller Builder |
I specifically wanted to avoid making it an array because then every function like this has to transform it into a record and the consumer has to transform the record into an array for each function like this. I don't follow on your generated identity column comment. As far as types concerned id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY is just column of type bigint, it being an identity is a domain/constraint detail. So my idea was that consumers can just SELECT id FROM tablename/temp_tablename and pass it as an argument to the function.
|
|
| Sep 27, 2022 at 18:02 | history | edited | user234725 | CC BY-SA 4.0 |
Don't cast NULL to bigint[]
|
| Sep 27, 2022 at 17:55 | history | answered | user234725 | CC BY-SA 4.0 |