FRiCKLE / ngx_postgres

upstream module that allows nginx to communicate directly with PostgreSQL database.
http://labs.frickle.com/nginx_ngx_postgres/
BSD 2-Clause "Simplified" License
545 stars 122 forks source link

postgres function returning json #53

Open houmanb opened 7 years ago

houmanb commented 7 years ago

I have a function returning a json defined in a 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 I have a nginx including the Postgres nginx Module (openresty) with the following 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"}
  ]^@

What am I doing wrong? I definitely miss something I'm not aware of. Best reagards