使用 PostgreSQL ARRAY、array_to_json()

Question

让我解释一下我的问题。我管理一个应用程序，该应用程序允许为用户赋予角色并让他们使用数据库访问不同的模式PostgreSQL。

我想查看每个用户的详细信息，所以我的意思是我想查看所有模式+角色。

所以我做了一个函数getList()，它返回登录名+密码+角色+模式。这是功能：

public static function getList()

{

    $db = DbConnect::getDb();

    $q = pg_query("SELECT

    u.usesysid as idUser, u.usename as login,

    u.passwd as password,

    ARRAY(SELECT b.rolname FROM pg_catalog.pg_auth_members m JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid) WHERE m.member = r.oid) as roles,

    ARRAY(SELECT nspname FROM pg_namespace pn WHERE pg_catalog.has_schema_privilege(r.rolname, nspname, 'USAGE')= true AND nspname <> 'information_schema' AND nspname !~~ 'pg_%') as schemas

    FROM pg_catalog.pg_roles r JOIN pg_catalog.pg_user u on(r.rolname=u.usename)

    WHERE usename !~ 'postgres' ORDER BY 1;");

    if ($data = pg_fetch_assoc($q)) {

        do {

            $users[] = new User($data);

        } while ($data = pg_fetch_assoc($q));

        return $users;

    } else {

        return null;

    }

}

但是，（据我所知），我需要在我的查询中放置 aarray_to_json()或json_build_array()类似的东西，因为查询中的数组 ( PostgreSQL) 返回 PHP 中的字符串而不是数组。

如你看到的 ：

  8 => 

    object(User)[9]

      private '_idUser' => string '24733' (length=5)

      private '_login' => string 'efefhhh' (length=7)

      private '_password' => string '********' (length=8)

      private '_roles' => string '{role1,role2}' (length=13)

      private '_schemas' => string '{schematest,public,schematest2}' (length=31)

所以我真的需要一个PHP数组来使下面的代码工作：

$list = UserManager::getList();

foreach ($list as $elt) {

    echo '<p>' . $elt->getLogin() . '</p>';

    echo '<p>' . $elt->getPassword() . '</p>';

    $roles=$elt->getRoles();

    //$roles=json_decode($roles); //Will be useful if I put a array_to_json() in the query of getList ?

    if(isset($roles) && !empty($roles)){

        echo '<p>Membership of :</p>';

        echo '<ul>';

        foreach ($roles as $role) {

            echo '<li>'.$role.'</li>';

        }

        echo '</ul>';

    }

}

但我真的不知道我可以在哪里放置任何array_to_json()或json_build_array()其他任何东西......有人可以帮助我吗？

Answer 1

我不必在 json 中编码。&我没有修改我的功能UserManager::getList();。我来给你展示 ：$list = UserManager::getList();foreach ($list as $elt) {&nbsp; &nbsp; echo '<p>' . $elt->getLogin() . '</p>';&nbsp; &nbsp; echo '<p>' . $elt->getPassword() . '</p>';&nbsp; &nbsp; $roles=$elt->getRoles();&nbsp; &nbsp; if(isset($roles) && !empty($roles) && $roles!=="{}"){&nbsp; &nbsp; &nbsp; &nbsp; $roles = trim($roles, "{}");&nbsp; &nbsp; &nbsp; &nbsp; $roles = explode (",", $roles);&nbsp; &nbsp; &nbsp; &nbsp; echo '<p>Membership of :</p>';&nbsp; &nbsp; &nbsp; &nbsp; echo '<ul>';&nbsp; &nbsp; &nbsp; &nbsp; foreach ($roles as $role) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; echo '<li>'.$role.'</li>';&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; echo '</ul>';&nbsp; &nbsp; }&nbsp; &nbsp; $schemas=$elt->getSchemas();&nbsp; &nbsp; if(isset($schemas) && !empty($schemas) && $schemas!=="{}"){&nbsp; &nbsp; &nbsp; &nbsp; $schemas = trim($schemas, "{}");&nbsp; &nbsp; &nbsp; &nbsp; $schemas = explode (",", $schemas);&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; echo '<p>Use schemas :</p>';&nbsp; &nbsp; &nbsp; &nbsp; echo '<ul>';&nbsp; &nbsp; &nbsp; &nbsp; foreach ($schemas as $schema) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; echo '<li>'.$schema.'</li>';&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; echo '</ul>';&nbsp; &nbsp; }}