以动态检索记录类型值

Question

我有一个函数，我在其中使用FOR循环与动态查询。不知道为什么，但postgres无法从记录变量检索单个字段...仍然有错误

SELECT (1,token).id - syntax error？

是否有机会从此获得字段值？

CREATE OR REPLACE FUNCTION mobile666(v_limit integer, v_offset integer) RETURNS void AS 
$BODY$ 
    DECLARE 
    r record; 
    x text[]; 
    kap text; 
    i_attrs text[] := ARRAY[ 
    ['test','id','value'], 
    ['test', 'id','value'], 
    ['test', 'id','value'], 
    ['test', 'id','value'] 
    ]; 
    quer text; 

    BEGIN 
    FOREACH x SLICE 1 IN ARRAY i_attrs LOOP 
    FOR r in EXECUTE 
    'SELECT * FROM ' || x[1]::regclass || ' WHERE ' || quote_ident(x[2]) || ' IS NOT NULL' LOOP 

    execute 'SELECT '|| r || '.' || quote_ident(x[2]) INTO kap; 
    RAISE NOTICE 'id %', kap; 
execute 'SELECT '|| r || '.' || quote_ident(x[5]) INTO kap; 
    RAISE NOTICE 'id %', kap; 

    END LOOP; 
    END LOOP; 

    END; 
    $BODY$ 
LANGUAGE plpgsql VOLATILE 
COST 400; 



CREATE TABLE test 
(
    id integer NOT NULL, 
    value text NOT NULL, 
    CONSTRAINT pk_test PRIMARY KEY (id) 
) 
WITH (
    OIDS=FALSE 
); 


INSERT INTO test(
      id, value) 
    VALUES (1,'token'),(2,'smt'),(3,'cocc'); 

Answer 1

试试这个：

CREATE OR REPLACE FUNCTION mobile666(v_limit integer, v_offset integer) RETURNS void AS 
$BODY$ 
DECLARE 
    r record; 
    x text[]; 
    kap text; 
    i_attrs text[] := ARRAY[ 
    ['test','id','value'], 
    ['test', 'id','value'], 
    ['test', 'id','value'], 
    ['test', 'id','value'] 
    ]; 
    quer text; 

BEGIN 
    FOREACH x SLICE 1 IN ARRAY i_attrs LOOP 
     FOR r in EXECUTE 
      'SELECT * FROM ' || x[1]::regclass || ' WHERE ' || quote_ident(x[2]) || ' IS NOT NULL' LOOP 

      execute 'SELECT ($1::text::' || x[1] || ').' || x[2] INTO kap USING r; 
      RAISE NOTICE 'id %', kap; 
      execute 'SELECT ($1::text::' || x[1] || ').' || x[3] INTO kap USING r; 
      RAISE NOTICE 'id %', kap; 
     END LOOP; 
    END LOOP; 
END; 
$BODY$ 
LANGUAGE plpgsql VOLATILE 
COST 400; 

线索是投射动态SQL字符串适当的表格（$1::text::table_name）内记录r（$1）。在此之后，您可以访问像($1::text::table_name).col_name这样的记录列，并且可以动态更改表名或列名。