1

I'm a SQL Server guy and I have a need to write some dynamic SQL in Postgres. Here's what I need. The dynamic SQL would be dependent upon integers produced by this query:

SELECT local_channel_id
FROM d_channels dc
INNER JOIN channel c ON c.id = dc.channel_id
                     AND c.name LIKE '%__Achv'

Using this, I need to build and execute a select and subsequent union select on the below query substituting the the values produced by the above query where indicated below by {X} (4 places):

SELECT
    dmc.message_id,
    dmm.received_date,
    dmm.server_id,
    dc.channel_id,
    dmcm."SOURCE",
    dmcm."TYPE",
    dmm.status,
    dmc.content
FROM 
    d_mc{X} dmc
INNER JOIN 
    d_mm{X} dmm ON dmc.message_id = dmm.message_id
INNER JOIN 
    d_channels dc ON dc.local_channel_id = {X}
INNER JOIN 
    d_mcm{X} dmcm ON dmcm.message_id = dmc.message_id
                  AND dmcm.metadata_id = 0
WHERE 
    dmm.connector_name = 'Source'
    AND dmc.content_type = 1 --Raw
    AND date(dmm.received_date) + interval '7' < now()

Can anybody help with this? I'm truly clueless when it comes to Postgres.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
rhess21
  • 41
  • 6
  • If you are asking how to execute dynamic SQL in PostgreSQL then there are plenty of resources and answers on this site alone. For example: http://stackoverflow.com/questions/12780275/dynamic-sql-query-in-postgres – Nick Jul 25 '16 at 23:28
  • 1
    [Partitioning](https://www.postgresql.org/docs/current/static/ddl-partitioning.html) – Abelisto Jul 25 '16 at 23:42
  • In which programming language do you want to write that? Or do you want to write it as a database function? – Laurenz Albe Jul 26 '16 at 06:46
  • A database function. – rhess21 Jul 26 '16 at 13:58
  • Also, I understand there are plenty of examples out there, but I haven't quite found one that matches my requirements and I'm having trouble adapting any examples I've found. – rhess21 Jul 26 '16 at 13:59

0 Answers0