从PHP查询时,PostgreSQL内部的regexp_matches视图始终返回null [英] Postgresql regexp_matches inside view always returns null when queried from PHP
问题描述
我有类似的视图
CREATE OR REPLACE VIEW regexp_test AS (
SELECT regexp_matches(decode('NTB4', 'base64')::text, '(\d+)x')
)
当我从pgAdmin查询视图时,按预期返回单个值为50的数组。
When I query view from pgAdmin, array with single value of 50 is returned, as expected.
SELECT * FROM regexp_test
但是当我通过 pg_query从PHP内部调用相同的查询时('SELECT * FROM regexp_test')
,什么也不会返回。
But when I call the very same query from within PHP via pg_query('SELECT * FROM regexp_test')
, nothing is returned.
postgres版本9.5.3,
php版本7.0.3(与5.6.14的结果相同)
PHP代码非常简单:
PHP code is very plain:
<?php
$link = pg_connect('host=localhost port=5432 dbname=test user=postgres password=postgres');
$qry = "SELECT * FROM regexp_test";
$res = pg_query($link, $qry);
while ($row = pg_fetch_row($res)) {
print_r($row);
}
推荐答案
我发现使用 encode(decode('NTB4','base64'),'escape')
而不是typecast decode('NTB4','base64'):: text
已解决问题。
I figured out using encode(decode('NTB4', 'base64'), 'escape')
instead of typecast decode('NTB4', 'base64')::text
fixed problem.
所以测试视图现在看起来像这样:
So test view now looks like this:
CREATE OR REPLACE VIEW regexp_test AS (
SELECT regexp_matches(encode(decode('NTB4', 'base64'), 'escape'), '(\d+)x')
)
调用 pg_query('SELECT * FROM regexp_test')
现在返回预期结果-单行/字段中包含'{50}'
。
Calling pg_query('SELECT * FROM regexp_test')
now returns expected result - single row/field with '{50}'
in it.
这篇关于从PHP查询时,PostgreSQL内部的regexp_matches视图始终返回null的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!