postgresql 动态返回结果集

沸羊羊 发布于 2015/11/20 13:34
阅读 1K+
收藏 0

华为云11月刊推送:DIY微信问答机器人,高性能计算代码的20个技巧!>>>

@kenyon_君羊 你好,想跟你请教个问题:

 

表A 结构:
CREATE TABLE t_data_fields
(
  id character varying(71) NOT NULL, -- ID
  data_type character varying(30) NOT NULL, -- 数据类型
  is_enabled smallint NOT NULL DEFAULT 1, -- 是否启用...
  fields_to_fields character varying(50),
  CONSTRAINT pk_data_fields PRIMARY KEY (id),
  CONSTRAINT unique_data_fields UNIQUE (data_type, field_name)
)

表B结构:
CREATE TABLE t_plu
(
  id character varying(36) NOT NULL, -- ID
  plu_no integer NOT NULL, -- PLU号
  item_no character varying(50) DEFAULT ''::character varying, -- 货号
  CONSTRAINT pk_plu PRIMARY KEY (id)
)

现在查询表A,获取fields_to_fields,查询出的数据,应该就是表B中的字段(表B中的字段数不止这些,这儿只是省略了)
再用查询出的数据,去查询表B,查出对应的数据,这个sql语句怎么实现?下面是我自己写的存储过程,能读取到数据,但是无法动态的查询到数据,只能写死了,如:select * from f_get_rows('PLU','t_plu') as plu(item_no character varying(50), plu_no integer);  其中,as 后面的应该是动态的,请问怎么修改?谢谢

create or replace function f_get_rows(num varchar,tableType varchar) returns setof record as
$$
declare
re record;
rec record;
sql varchar;
str varchar;
begin
str='';
        sql = 'select fields_to_fields from t_data_fields where is_enabled = 1 and fields_to_fields is not null and data_type=''' || num ||'''';
        for re in execute sql loop
           if str='' then
  if re.fields_to_fields = '' then

  else
      str = re.fields_to_fields;
      end if;
           else
  if re.fields_to_fields = '' then
  else
      str = str ||','|| re.fields_to_fields;
      end if;
           end if;
        end loop;
       raise notice 'print %',str;
        sql = 'select ' || str || ' from ' || tableType || ';';
        RAISE NOTICE 'i want to print %', sql;
        for rec in execute sql loop
        return next rec;
        end loop;
return;
end
$$
language 'plpgsql';

加载中
返回顶部
顶部