Use variables in side insert or update query in postgres

Question

I am working on sql script in which i need to insert record in the db only if record is missing in the table , if record do exists in the table i need to update the template Here is my sql query

insert into deployment.nodes values('sind','11', now(),'temp',
   '<table id="producttable">
 <thead>
    <tr>
  <td>UPC_Code</td>
  <td>Product_Name</td>
  </tr>
  </thead>
 <tbody>
   <!-- existing data could optionally be included here -->
 </tbody>
</table>

<template id="productrow">
<tr>
 <td class="record"></td>
   <td></td>
 </tr>
 </template> ')  on conflict on constraint nodes_pkey 
DO UPDATE SET latest=now(), status='active',
agent='<table id="producttable">
<thead>
<tr>
  <td>UPC_Code</td>
  <td>Product_Name</td>
  </tr>
  </thead>
<tbody>
 <!-- existing data could optionally be included here -->
 </tbody>
</table>
<template id="productrow">
  <tr>
   <td class="record"></td>
     <td></td>
    </tr>
 </template>'

This sql script is working fine for me but i want to use HTML stuff in a variable to minimize the length of sql script.

lets say

var template = '<html> ....  </html>'  and then query will be like 
insert into deployment.nodes values('sind','11', now(),'temp',template)  on conflict on constraint 
nodes_pkey   DO UPDATE SET latest=now(), status='active',agent=template

set agent = EXCLUDED.agent See here: postgresql.org/docs/current/sql-insert.html — Mike Organek
– Mike Organek, Commented Dec 3, 2020 at 9:32
@MikeOrganek couldn't understand from the ink could you please give a small example — Samrat Rahul Tiwari
– Samrat Rahul Tiwari, Commented Dec 3, 2020 at 9:37

Mike Organek · Accepted Answer · 2020-12-03 09:41:23Z

3

Example of using the EXCLUDED pseudotable:

insert into deployment.nodes values('sind','11', now(),'temp',
   '<table id="producttable">
 <thead>
    <tr>
  <td>UPC_Code</td>
  <td>Product_Name</td>
  </tr>
  </thead>
 <tbody>
   <!-- existing data could optionally be included here -->
 </tbody>
</table>

<template id="productrow">
<tr>
 <td class="record"></td>
   <td></td>
 </tr>
 </template> ')  on conflict on constraint nodes_pkey 
DO UPDATE 
  SET latest = EXCLUDED.latest, 
      status = EXCLUDED.status,
      agent = EXCLUDED.agent

answered Dec 3, 2020 at 9:41

Mike Organek

12.6k3 gold badges15 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Use variables in side insert or update query in postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related