openresty upstream postgres calling a function whi

2019-07-22 05:54发布

问题:

Suppose you have a function returning a json defined in your postgresQL database.

CREATE OR REPLACE FUNCTION test() RETURNS JSON AS $$
  SELECT
  '[
    {"fName":"John","lName":"Doe"},
    {"fName":"Jane","lName":"Doe"}
  ]'::JSON;
$$ 
LANGUAGE SQL STRICT IMMUTABLE;


SELECT test();
-------------------------------------
 [                                  
     {"fName":"John","lName":"Doe"},
     {"fName":"Jane","lName":"Doe"} 
 ]

Furthermore you have a Nginx including the Postgres Nginx Module (openresty) with the follwoing config file:

worker_processes  1;
error_log logs/error.log;
events {
    worker_connections 1024;
}
http {

    upstream database {
        postgres_server localhost dbname=example user=postgres;
        postgres_keepalive max=200 overflow=reject;
    }

    server {
        listen 8080;
        location /test/ {
                postgres_pass database;
                rds_json  on;
                postgres_query    HEAD GET  "SELECT test()";
                postgres_rewrite  HEAD GET  no_rows 410;
        }
    }
}

With rds_json on; all quotes are escaped in the output and it looks like this:

curl http://localhost:8080/test/
[{"test":"[\n    {\"fName\":\"John\",\"lName\":\"Doe\"},\n    {\"fName\":\"Jane\",\"lName\":\"Doe\"}\n  ]"}]

An if I set rds_json off; I receive a properly formatted json but the return string starts and ends with some awkward signs:

@^C^@^@^@^@^@^@^B^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^A^@^@<80>r^@^D^@test^AL^@^@^@[
    {"fName":"John","lName":"Doe"},
    {"fName":"Jane","lName":"Doe"}
  ]^@

Is there a way to either get rid of the additional signs in the result object or do not escape all the double quotes in the first case?

Thanks

回答1:

I've found a reason. Actually it was trivial. Instead of making openresty format the output to json. Sorry for bothering the community with my question. I had to set the header correctly:

worker_processes  1;
error_log logs/error.log;
events {
    worker_connections 1024;
}
http {

    upstream database {
        postgres_server localhost dbname=example user=postgres;
        postgres_keepalive max=200 overflow=reject;
    }

    server {
        listen 8080;
        location /test/ {
                postgres_pass database;
                postgres_query    HEAD GET  "SELECT test()";
                more_set_headers  'Content-Type: application/json';
                postgres_rewrite  HEAD GET  no_rows 410;
        }
    }
}