寻找一种在执行前重写Postgresql查询的方法 [英] Looking for a way to rewrite Postgresql query before executing it

问题描述

我有一个Postgres客户发送类似的查询

I have a Postgres client sending queries like

SELECT ... FROM "public"."mycontent" "mycontent"
WHERE (strpos(substring("mycontent"."summary" from 1), 'search_string') + 1 - 1 > 0)

到我们的Postgres服务器.我希望客户端使用我的全文本搜索功能，但是我无法访问该客户端的代码.因此，我正在寻找一种将上述形式的所有传入查询重写为类似方式的方法:

to our Postgres server. I want the client to use my full text search function, but I have no access to the client's code. So I am looking for a way to rewrite all incoming query in the above form to something like:

SELECT ... FROM "public"."mycontent" "mycontent"
WHERE id in full_text_search('search_string')

请注意，提取了"search_string"，因此此处无法使用Postgres规则，因为它们不进行此类提取.我希望任何人都知道可以进行查询重写的任何postgres中间件或代理，或者还有其他更好的主意吗?谢谢.

Note the extraction of the 'search_string', so Postgres Rules cannot be used here because they don't do such extraction. I hope anyone knows of any postgres middleware or proxy that can do query rewrite, or is there any other better idea? Thank you.

推荐答案

我想我必须回答自己的问题.我使用python gevent套接字编程实现了一个Postgres代理服务器来重写查询.请注意，如果连接使用SSL，这将不起作用.

I guess I have to answer my own question. I implemented a postgres proxy server for rewriting query, using python gevent socket programming. Note this doesn't work if connection uses SSL.

from gevent import socket, server, Greenlet, joinall

def pipe(source_socket, destination_socket, modify=False):
    while True:
        try:
            data = source_socket.recv(1024)
        except socket.error, e:
            break
        else:
            if data:
                if modify: data = data.replace("limit 10", "limit 1 ")
                destination_socket.send(data)
            else:
                break

def pg_proxy(client_socket, address):
    pg_socket = socket.create_connection(("localhost", 5432))
    pg_socket.settimeout(300.0)
    client_socket.settimeout(300.0)
    joinall((
        Greenlet.spawn(pipe, client_socket, pg_socket, modify=True),
        Greenlet.spawn(pipe, pg_socket, client_socket, modify=False)
    ))
    pg_socket.close()
    client_socket.close()

if __name__ == '__main__':
    s = server.StreamServer(("localhost", 5433), pg_proxy)
    s.serve_forever()

这篇关于寻找一种在执行前重写Postgresql查询的方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！