title: From C, get Postgres notification with row as payload
body: <p>I'm trying to create a postgres function that sends a notification to my C code where the payload string is the current value of a row in a table.</p>
  
  <p>The C side is no problem. I have something like this:</p>
  
  <pre><code>// postgres setup ...
  PGnotify *notify = PQnotifies(conn)
  printf("Notification: '%s'\n", notify-&gt;extra); 
  </code></pre>
  
  <p>I want this to print something like: <code>Notification: MyTable{"ID":123,"Value":9,"Status":false}</code>.</p>
  
  <p>On the Postgres side of things, I can get a particular row as JSON with</p>
  
  <pre><code>select row_to_json(row) 
  from (
      SELECT * FROM "MyTable" WHERE "MyTable"."ID"=123
  ) row;
  </code></pre>
  
  <p>But I can't figure out how to do this within a function and save the json result of that select to a local variable. I would expect the following to work ...</p>
  
  <pre><code>do language plpgsql $$ 
  begin  
    myjson := (select row_to_json(row)  from (SELECT * FROM "MyTable" WHERE "MyTable"."ID"=123) row);
    -- myrow := ... somehow convert myjson to text ...
    -- pg_notify('mychannel', mytable || myrow);
  end; 
  $$;
  </code></pre>
  
  <p>... but I get the error "myjson is not a known variable". My postgres fu is weak so I'm sure this a simple syntax error but I can't figure it out. I tried 'select into' but couldn't make it work either. </p>
  
  <p>Also, you can see from the commented lines that my intention is to somehow convert that json to text but I'm not sure how to accomplish that.</p>
  
  <p>Any help is appreciated, thanks!</p>