1

I am writing my first plperl function in Postgres and I have a need to access some values in the current_settings() area (by use of that call) -- and I am wondering what the best solution to doing so is?

In plpgsql I can do something like:

DECLARE
  cid int;
BEGIN
  select nullif(current_setting('jwt.claims.customerId', true), '') :: int into cid;
END ...

Just wondering about the equivalent of accessing system functions like current_setting within a Perl plperl script..

THanks!

sjmcdowall
  • 1,471
  • 4
  • 15
  • 27

1 Answers1

1

Use one of the database access functions, e.g.:

$rv = spi_exec_query("select nullif(current_setting('jwt.claims.customerId', true), '')::int as cid");
$cid = $rv->{rows}[0]->{cid};
klin
  • 112,967
  • 15
  • 204
  • 232
  • Well -- ugh -- I feel like a sort of doh moment! That makes total sense.. for some reason I was thinking internal functions would be "different". Overthinking perhaps! Thanks! – sjmcdowall Nov 06 '18 at 19:58
  • I am getting a syntax error near/at the $q$ part .. is that ..ah.. legal for within a plperl function?? The error is: ERROR: syntax error at line 9, near "$q$.. The code is: # Ok -- get our customerId from the current_settings.. $rv = spi_exec_query($q$ select nullif(current_setting('jwt.claims.customerId', true), '')::int as cid $q$); – sjmcdowall Nov 06 '18 at 20:42
  • I've tried to use postgres quotes in a pearl function... Sorry, the answer updated. – klin Nov 06 '18 at 20:59
  • Thanks -- I thought as much -- too bad, those $Q$ type quote things are handy! :) – sjmcdowall Nov 07 '18 at 13:41