I’m being required to create a function that transforms a single column’s value based on the user’s input. I need some help on the syntax for doing so. Here is the query I’m currently performing to get the rows: some pseudocode on what I’m trying to do: I’ve been trying to …
Tag: set-returning-functions
Function returns row type as single value instead of one value per field
The only time I encountered a similar problem on SO is on here, but the proposed solution was not relevant for me. I call a PostgreSQL function, but it’s returning a single value instead of one value per field, which I can access. I have a table “Words”, which consists of a primary key word …
How to join generate_series and an array
I want to generate a list of columns using generate_series() and joining it to a specific array (a list of value, e.g. [1, 2, 3]), and my expected result looks like this: Day | Code 2019-08-01 …
RETURNING rows using unnest()?
I’m trying to return a set of rows after doing UPDATE. Something like this. but postgres complains, rightly so: set-valued function called in context that cannot accept a set How am I supposed to go about implementing this? That is, RETURNING a set of rows from SELECTed array after UPDATE? I’m awa…
Split given string and prepare case statement
Table: table_name Insertion of records: Now I want to update set_name for some dates. For example: I want to update table like this: Note: The given_dates and set_name are pass a parameter because of they are dynamic. I may pass 2 sets as shown above s1,s2 or may pass 4 sets according to the requirement. So I…
What’s the proper index for querying structures in arrays in Postgres jsonb?
I’m experimenting with keeping values like the following in a Postgres jsonb field in Postgres 9.4: I’m executing queries like: How would I create an index on that data for queries like the above to utilize? Does this sound reasonable design for a few million rows that each contain ~10 events in t…
PostgreSQL – order by an array
I have 2 tables – course that contains id and name of the courses and tagCourse that contains tags for each course. I’d like to write a function that searches courses by given array of tags and returns them ordered by quantity of matching tags. However I don’t know how to write it correctly …
PostgreSQL unnest() with element number
When I have a column with separated values, I can use the unnest() function: How can I include element numbers? I.e.: I want the original position of each element in the source string. I’ve tried with window functions (row_number(), rank() etc.) but I always get 1. Maybe because they are in the same row…