Is it postgres_set works?

[xronos]

I had tried this configuratio for my project:

postgres_pass database; postgres_query "SELECT id FROM articles WHERE slug='$slug' LIMIT 1"; postgres_set $id 0 0; postgres_output none;

When i try to use $id for rewrite operator it returns nothing: rewrite ^/(.*)$ /$id permanent; => redirects to "/" but not /$id

When i try echo it, it returns nothing: echo "id = '$id'"; => id=''

But when i try example from your tests with header, it works: add_header "X-ID" $id;

Why it is? Why i can`t get variable value?

[PiotrSikora]

Regarding rewrite: This is because of how nginx works internally. rewrite is evaluated before the content handler (postgres_*) is, which means that $id isn't available (yet) when rewrite runs.

It is still possible to use value received from PostgreSQL database like in your example, you'll just need to combine ngx_postgres with agentzh's fork of eval module:

location / {
    eval_subrequest_in_memory  off;
    eval $id {
        postgres_pass    database;
        postgres_query   "SELECT id FROM articles WHERE slug='$slug' LIMIT 1";
        postgres_output  value  0 0;
    }
    rewrite ^ /$id permanent;
}

Please note that eval module currently doesn't work with nginx-0.8.42, so you'll need to use older version of nginx.

Also, depending on your real use case, you might want to use ngx_http_auth_request_module or ngx_lua instead.

---

Regarding echo: This is again because of how nginx works internally. There can be only one content handler active at a time. Both postgres_* and echo are content handlers (and apparently both do too little to check if other content handler is already set). This means that in your configuration only echo content handler is being executed and since postgres_* isn't executed, postgres_set can't set $id.

Again, you can combine ngx_postgres with any of the modules mentioned above to echo received value.

Hope this clarify things.

[xronos]

I have nginx 0.7.67 installed on freebsd. I compile nginx with both postgres and agentzh-eval modules. I have tried first variant:

location ~* ^.*-art-(\S).html$ { set $slug $1;

eval_subrequest_in_memory off;
eval $id {
     postgres_pass    database;
     postgres_query   "SELECT id FROM articles WHERE slug='$slug' LIMIT 1";
     postgres_output  value 0 0;
}

rewrite ^(.*)$ /ads/id-$id.html permanent;

}

But the result was same. $id was empty in rewrite. I put /ads/ad-.html and it was showed.

I want to build smart cache with postgres and ssi module. The simple db request should return ad id and then it should be located on the disk by $id.

[PiotrSikora]

Sorry, I was wrong. You cannot use $variables set outside of eval block within it. This is documented in eval module's documentation.

However, you can do something like:

location / {
    eval_subrequest_in_memory  off;
    eval $id {
        postgres_pass    database;
        postgres_query   "SELECT id FROM articles WHERE uri='$request_uri'";
        postgres_output  value 0 0;
    }
    rewrite ^ /$id permanent;
}

or what is probably even better solution (but requires writing stored procedure in PostgreSQL):

location / {
    eval_subrequest_in_memory  off;
    eval $id {
        postgres_pass    database;
        postgres_query   "SELECT get_article_id_for_uri('$request_uri')";
        postgres_output  value 0 0;
    }
    rewrite ^ /$id permanent;
}

Otherwise you should wait until ngx_lua will allow ngx.location.capture() to be used within set_by_lua directive.

[xronos]

I have tried exactly right this location / { eval_subrequest_in_memory off; eval $id { postgres_pass database; postgres_query "SELECT id FROM articles WHERE uri='$request_uri'"; postgres_output value 0 0; } rewrite ^ /$id permanent; } And it doesn` t work. Rewrite operator redirects browser on the page without $id.

[PiotrSikora]

Does this query return anything for you? Because if it doesn't then $id is empty.

I've just created articles table and filled it with sample data:

create table articles (id integer, uri text);
insert into articles values (5, '/home');

Accessing http://localhost/home redirects me to http://localhost/5, so everything works as expected.

[xronos]

thank you for answer, too sad it doesn`t work for me

[joe-at-startupmedia]

I was able to get this working with the following stack:

nginx-eval-module-2016.06.10  
openssl-1.0.2l
echo-nginx-module-0.61  
ngx_devel_kit-0.3.0           
pcre-8.41                   
zlib-1.2.11
nginx-1.13.6            
ngx_postgres-1.0rc7           
set-misc-nginx-module-0.31

Url trigger somehost.com?subentity_slug=foo&entity_id=bar

  location /subentity {
    eval_subrequest_in_memory off;

    eval $subentity_id {
      postgres_pass pg_backend;
      postgres_query  "SELECT subentity_id from entity_routing where 
          slug = '$arg_subentity_slug' and entity_id = '$arg_entity_id'";
      postgres_output value;
    }

    if ($subentity_id = "") {
      return 404;
    }

    if ($subentity_id != "") {
      return 301 http://someotherbiz.com/?entity_id=$arg_entity_id&subentity_id=$subentity_id;
    }
  }