Skip to content
Advertisement

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 need the dynamic case statement for update the set_name.

Given two parameters:

Well I can do this by using following static script:

Static Update statement:

The above update statement does the job done but statically.

Like the same way to update table I want to prepare only case statement which should be dynamic which can change as per the parameters (p_dates,p_sets) changes.

Questions:

  1. How to split the given dates that is p_dates? (I have to keyword in between two dates.)
  2. How to split the given sets that is p_sets? (I have ‘,’ comma in between two set_names.)
  3. How to prepare dynamic case statement after splitting the p_dates and p_sets?

This question relates to Dynamic case statement using SQL Server 2008 R2, which is the same thing but for Microsoft SQL Server.

Advertisement

Answer

Clean setup:

Use a singular term as column name for a single value.
The data type is obviously date and not a timestamp.

To transform your text parameters into a useful table:

“Parallel unnest” is handy but has its caveats. Postgres 9.4 adds a clean solution, Postgres 10 eventually sanitized the behavior of this. See below.

Dynamic execution

Prepared statement

Prepared statements are only visible to the creating session and die with it. Per documentation:

Prepared statements only last for the duration of the current database session.

PREPARE once per session:

Or use tools provided by your client to prepare the statement.
Execute n times with arbitrary parameters:

Server-side function

Functions are persisted and visible to all sessions.

CREATE FUNCTION once:

Call n times:

SQL Fiddle

Superior design

Use array parameters (can still be provided as string literals), a daterange type (both pg 9.3) and the new parallel unnest() (pg 9.4).

<@ being the “element is contained by” operator.

Call:

Details:

User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement