Convert SQL View creation with switch case to Postgresql

Question

I am new to PostgreSQL syntax. I have this MS SQL Server query to create VIEW with a `CASE.

What will the equivalent Postgres query be?

CREATE VIEW QUAD_TEXT AS
SELECT
  GRAPH_ID,
  SUBJECT,
  SUBJECT_TYPE,
  PREDICATE,
  OBJECT_URI,
  OBJECT_VALUE,
  OBJECT_DATATYPE,
  (CASE '"'+OBJECT_VALUE+'"' WHEN '""'
           THEN
               (CASE '<'+OBJECT_URI+'>' WHEN '<>'
                THEN '""'
                ELSE '<'+OBJECT_URI+'>' END)
            ELSE
        '"'+OBJECT_VALUE+'"'
        +(CASE '"'+OBJECT_DATATYPE+'"' WHEN '""'
          THEN '""'
          ELSE (CASE
              WHEN OBJECT_DATATYPE LIKE 'http%' THEN '^^<'+OBJECT_DATATYPE+'>'
              ELSE '@'+OBJECT_DATATYPE+'' END)
          END)
        END) AS OBJECT,
  STATUS
FROM TUPLES
WHERE ((CONVERT(bigint,CONVERT(VARBINARY(8),CONTEXT_INFO()))) & GA) > 0
 AND VERSION = '0';

https://www.rebasedata.com/convert-tsql-to-postgres-online ? — Vitaly Borisov, Jun 07 '19 at 03:20
Please explain what `convert(varbinary(8), context_info)` is supposed to be doing. I have no idea what the equivalent is in Postgres. — Gordon Linoff, Jun 07 '19 at 12:08
You need to migrate the logic behind [context_info usage](https://stackoverflow.com/questions/696200/whats-the-postgresql-equivalent-of-mssqls-context-info) BEFORE you attempt to convert queries. — SMor, Jun 07 '19 at 12:21

score 1 · Answer 1 · edited Jun 07 '19 at 09:18

Microsoft SQL Server uses + for string concatenation.
Postgres complies with the SQL standard and uses || for string concatenation,

Alternatively we can use the CONCAT function in Postgres.

using ||: 'Post' || 'gres' ---> Postgres
using CONCAT: CONCAT('Post', 'gres') ---> Postgres

So in your SQL query you can either use || or CONCAT instead of +

For more details check out these links:

score 0 · Answer 2 · edited Jun 07 '19 at 09:15

PostgreSQL complies with the SQL standard and uses || for concatenating strings instead of +.

Hence, equivalent PostgreSQL script would be:

CREATE VIEW QUAD_TEXT AS
SELECT
  GRAPH_ID,
  SUBJECT,
  SUBJECT_TYPE,
  PREDICATE,
  OBJECT_URI,
  OBJECT_VALUE,
  OBJECT_DATATYPE,
  (CASE '"'||OBJECT_VALUE||'"' WHEN '""'
           THEN
               (CASE '<'||OBJECT_URI||'>' WHEN '<>'
                THEN '""'
                ELSE '<'||OBJECT_URI||'>' END)
            ELSE
        '"'||OBJECT_VALUE||'"'
        ||(CASE '"'||OBJECT_DATATYPE||'"' WHEN '""'
          THEN '""'
          ELSE (CASE
              WHEN OBJECT_DATATYPE LIKE 'http%' THEN '^^<'||OBJECT_DATATYPE||'>'
              ELSE '@'||OBJECT_DATATYPE||'' END)
          END)
        END) AS OBJECT,
  STATUS
FROM TUPLES
WHERE ((CAST(CAST(current_setting('CONTEXT_INFO') as bit(8)) as bigint)) & GA) > 0
 AND VERSION = '0';

Extending this slightly, the selection on the concatenation operator (||) vs the concatenation function (concat) may depend on the expected values of the operands; they do not produce the same results when an operand in NULL. In particular the predicate CASE '"'||OBJECT_VALUE||'"' WHEN '""' will produce NULL if object_value is null while concat('"',OBJECT_VALUE,'"') produces "". — Belayer, Jun 09 '19 at 20:11

Convert SQL View creation with switch case to Postgresql

2 Answers2