Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

postgres function returning json #53

Open
houmanb opened this issue Dec 4, 2016 · 0 comments
Open

postgres function returning json #53

houmanb opened this issue Dec 4, 2016 · 0 comments

Comments

@houmanb
Copy link

houmanb commented Dec 4, 2016

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

1 participant