sql - 插入语句在函数内部不起作用(PostgreSQL)

Question

我有一个用 PostgreSQL 编写的函数，用于遍历一个大表并将大量值插入到另一个表中。输出很好，显然插入了很多行，但实际上没有值插入目标表(我的代码中的“资源”表)。

我曾尝试将插入语句放入事务中，但无济于事。我是否缺少某种模糊的访问或权限设置？我在网上找到了几个像我一样这样做的例子，所以我对这个有点担心......

这是我的功能:

DECLARE
datatype_property record; 
property record;
new_resource_id bigint;
BEGIN  
    RAISE NOTICE 'Starting...';
    FOR datatype_property IN  
      SELECT * FROM datatype_properties
    LOOP  
        RAISE NOTICE 'Trying to insert';


        if not exists(select * from resources where uri = datatype_property.subject_resource) then
              SELECT INTO new_resource_id NEXTVAL('resources_id_seq');  
              INSERT INTO resources (id, uri) VALUES(  
                    new_resource_id,    
                    datatype_property.subject_resource
              );   
            RAISE NOTICE 'Inserted % with id %',datatype_property.subject_resource, new_resource_id;
        end if;
    END LOOP; 

 FOR property IN  
      SELECT * FROM properties 
 LOOP  

        if not exists(select * from resources where uri = property.source_uri) then
                SELECT INTO new_resource_id NEXTVAL('resources_id_seq');
              INSERT INTO resources (id, uri) VALUES(  
                        new_resource_id,
                        resource.source_uri
              ) ;   
                RAISE NOTICE 'Inserted % with id %',resource.source_uri, new_resource_id;
        end if;
        if not exists(select * from resources where uri = property.destination_uri) then
                SELECT INTO new_resource_id NEXTVAL('resources_id_seq');
              INSERT INTO resources (id, uri) VALUES(  
                        new_resource_id,
                        resource.source_uri
              ) ;   
        RAISE NOTICE 'Inserted % with id %',resource.source_uri, new_resource_id;
        end if;
 END LOOP;  
 RETURN;  

结束；

编辑:我已经使用以下链接中的说明激活了 plpgsql 语言:

http://wiki.postgresql.org/wiki/CREATE_OR_REPLACE_LANGUAGE

编辑 2:

这段代码:

DECLARE
datatype_property record; 
property record;
new_resource_id bigint;
BEGIN  

    insert into resources (id, uri) values ('3', 'www.google.com');
END

也不行:O

Answer 1

你的问题听起来好像你没有提交你的交易(正如 Pavel 指出的那样)或者你用来检查行的工具是例如使用 REPEATABLE READ 作为其隔离级别或某种缓存。

但是您的函数并不是一个好的开始解决方案。在循环中逐行插入行总是一个坏主意。这将比执行单个插入要慢得多(并且可扩展性更差)。

如果我没记错的话，这两个循环可以改写成如下语句:

insert into resource (id, uri)
select NEXTVAL('resources_id_seq'),
       dt.subject_resource
from datatype_properties dt
where not exists (select 1
                  from resources r
                  where r.uri = dt.subject_resource);


insert into resources (id, uri)
select nextval('resources_id_seq'),
       p.source_uri
from properties p
where not exists (select 1 
                  from resources r 
                  where r.uri = p.source_uri
                     or r.uri = p.destinatioin_uri);