How to use output parameter from webhook node in postgres query?

Hi,

I am working on a workflow to display the tables and the data inside a table from postgres database. The objective is to receive the schema name and table name from postman (will pass these values as Params in the GET request) and to use these values in the query.

eg: If I want to run the query select * from information_schema.tables where table_schema=‘schema’ , I have to receive the schema-name from postman.

How can I achieve this in n8n. Can I utilize the query parameter feature of the node?

I have my workflow added as well.

{
  "nodes": [
    {
      "parameters": {
        "path": "8ed4a745-7aa8-4763-a2af-663019e004fb",
        "responseMode": "lastNode",
        "responseData": "allEntries",
        "options": {
          "responseHeaders": {
            "entries": []
          }
        }
      },
      "name": "Webhook",
      "type": "n8n-nodes-base.webhook",
      "typeVersion": 1,
      "position": [
        420,
        370
      ],
      "webhookId": "8ed4a745-7aa8-4763-a2af-663019e004fb"
    },
    {
      "parameters": {
        "values": {
          "string": [
            {
              "name": "schema",
              "value": "={{$node[\"Webhook\"].json[\"query\"][\"schema\"]}}"
            },
            {
              "name": "table",
              "value": "={{$node[\"Webhook\"].json[\"query\"][\"table\"]}}"
            },
            {
              "name": "query",
              "value": "={{$json[\"query\"][\"query\"]}}"
            }
          ]
        },
        "options": {}
      },
      "name": "Set",
      "type": "n8n-nodes-base.set",
      "typeVersion": 1,
      "position": [
        660,
        370
      ]
    },
    {
      "parameters": {
        "operation": "executeQuery",
        "query": "select * from information_schema.tables  where table_schema='public'",
        "additionalFields": {
          "queryParams": "={{$node[\"Set\"].json[\"schema\"]}}"
        }
      },
      "name": "Postgres",
      "type": "n8n-nodes-base.postgres",
      "typeVersion": 1,
      "position": [
        880,
        370
      ],
      "credentials": {
        "postgres": "Postgres account"
      }
    },
    {
      "parameters": {
        "operation": "executeQuery",
        "query": "SELECT * FROM temperature2;",
        "additionalFields": {}
      },
      "name": "Postgres1",
      "type": "n8n-nodes-base.postgres",
      "typeVersion": 1,
      "position": [
        1080,
        370
      ],
      "credentials": {
        "postgres": "Postgres account"
      }
    }
  ],
  "connections": {
    "Webhook": {
      "main": [
        [
          {
            "node": "Set",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Set": {
      "main": [
        [
          {
            "node": "Postgres",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Postgres": {
      "main": [
        [
          {
            "node": "Postgres1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}

Hey @Dhanya_V_Sagar,

Hope you’re doing well :slight_smile:

You can use the Expression Editor to use the values you pass with the query parameters. The expression for the query can be as follow:

select * from {{$json.table}}.tables  where table_schema='{{$json.schema}}'

Hope this is what you’re looking for. Let us know if you need more help :slight_smile:

Hi,

Thank you for the quick response.

select * from {{$node[“Webhook”].json[“query”][“table”]}}.tables
select * from {{$node[“Webhook”].json[“query”][“table”]}}

I had tried these expressions before but it displays errors

error: syntax error at or near "."

and

error: syntax error at or near ";"

respectively.

This would be PostgreSQL complaining about some malformed query. Which values would {{$node["Webhook"].json["query"]["table"]}} have in your case?

The node contains the name of table.

So an expression like this would be working in principle if temperature2 is a table in the current schema:

What I suspect is happening here is that your first PostgreSQL node returns more than one item, meaning your expression won’t work anymore beyond the first item (since your are referring to items from the Webhook node which has only one item).

So you’d need to find out what you want to do here. If the second PostgreSQL node (“Postgres1”) only needs to run once (for the first item), just enable the respective setting:

image

If you want it to use each input from the first PostgreSQL node (“Postgres”), you’d need to make sure your expressions refers either to its own input data or to a node with the same number of items. For example like so:

Hi.
Thank you for the detailed explanation. I have enabled the execute once option and it works fine.

I have one more doubt. Say I want to choose a value from the input data of the node, like the image you have shared (you have selected migration). The input data is displaying only the first row , with its column values. If I want to select data from some other row, how can I achieve this (say row num 3) ?

This is not currently possible through the expression editor as it just previews the first item. You could manually write an expression including $item to reference a specific item though.