CODEWITHSUNDEEP
postgresqlplpgsql
Philip
Philip

Reputation: 1606

From C, get Postgres notification with row as payload

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.

The C side is no problem. I have something like this:

// postgres setup ...
PGnotify *notify = PQnotifies(conn)
printf("Notification: '%s'\n", notify->extra);

I want this to print something like: Notification: MyTable{"ID":123,"Value":9,"Status":false}.

On the Postgres side of things, I can get a particular row as JSON with

select row_to_json(row) 
from (
    SELECT * FROM "MyTable" WHERE "MyTable"."ID"=123
) row;

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 ...

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; 
$$;

... 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.

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.

Any help is appreciated, thanks!

Upvotes: 0

Views: 497

Answers (1)

Philip
Philip

Reputation: 1606

Apparently I needed to declare the variable in it's own block. I guess it handles the text conversion on its own. Also, I had to add perform to the notify command to get it to not complain about an unused result. This is what I ended up with:

do language plpgsql $$ 
declare 
  mytable := "MyTable"
  myjson text;
begin  
  myjson := (select row_to_json(row)  from (SELECT * FROM "MyTable" WHERE "MyTable"."ID"=123) row);
  perform pg_notify('mychannel', mytable||myjson);
end; 
$$;

In order to use the variable mytable in the query, I ended up looking at the postgres documentation on Executing Dynamic Commands and finding out I needed to do it this way:

do language plpgsql $$ 
declare 
  mytable := "MyTable"
  myjson text;
begin  
  EXECUTE 
    format('(select row_to_json(row)  from (SELECT * FROM %I WHERE %I."ID"=101) row);', mytable, mytable)
    INTO myjson;
  perform pg_notify('mychannel', mytable||myjson);
end; 
$$;

Upvotes: 1

Related Questions