数组迭代？ [英] array iteration?
本文介绍了数组迭代？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
是否有可能在plpgsql中迭代数组？
类似于：
函数insert_stuff（rel_ids int []）
....
foreach rel_ids as id
insert into table（rel_id，val）
values（id ，5）;
__________________________________
你是Yahoo！？
免费弹出窗口阻止程序 - 立即获取
http://companion.yahoo.com/
---------------------------（播出结束）-------------- -------------
提示1：订阅和取消订阅命令转到 ma ******* @ postgresql.org
Is it possible to iterate over an array in plpgsql?
Something like:
function insert_stuff (rel_ids int[])
....
foreach rel_ids as id
insert into table (rel_id, val)
values (id, 5);
__________________________________
Do you Yahoo!?
Free Pop-Up Blocker - Get it now
http://companion.yahoo.com/
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org
推荐答案
是的。
http://archives.postgresql.org/ pgsql ... 1 / msg00852.php
周六，2003-11-22 a t 12:44，CSN写道：
Yep.
http://archives.postgresql.org/pgsql...1/msg00852.php
On Sat, 2003-11-22 at 12:44, CSN wrote:
是否有可能在plpgsql中迭代一个数组？
类似于：
函数insert_stuff（rel_ids int []）
...
foreach rel_ids as id
插入表格（rel_id，val）
值（id，5）;
__________________________________
你是雅虎吗？？
免费弹出窗口阻止程序 - 立即获取
http://companion.yahoo.com/
------------------------ ---（广播结束）---------------------------
提示1：订阅和取消订阅命令转到 ma ******* @ postgresql.org
Is it possible to iterate over an array in plpgsql?
Something like:
function insert_stuff (rel_ids int[])
...
foreach rel_ids as id
insert into table (rel_id, val)
values (id, 5);
__________________________________
Do you Yahoo!?
Free Pop-Up Blocker - Get it now
http://companion.yahoo.com/
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org
--- ------------------------（播出结束）--------------------- ------
提示1：订阅和取消订阅命令转到 ma ***** **@postgresql.org
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org
您好： ）
有人询问PgSql [PG]中大对象[LO]的性能。它对我来说很有意思，因为我还没有和他们一起工作，我会很快就会花掉b $ b。我尝试搜索网络，文档和邮件列表，但我没有发现任何足够的回复。我会很开心，有些人，他们有经验
与他们（或谁知道PG内部:)可以使它干净。我想我不是单独的b $ b，对这个话题感兴趣。
在过去的时间里，行数据受到单个tupple（8K-32K）的限制），LO
真的需要，如果你想每行存储超过8K。文档中描述了较旧的
实现问题。
今天，每行有1G限制。是否有理由使用LO代替
将简单的文本存储到数据库表中？ （让我们忽略对LO的2G限制，
这可能会使LO在某些情况下变得有趣:)文档说它是
obsolvete。但是文档也没有说明性能，
关于使用等的建议。
通过访问二进制文件，我明白了：
- 阅读（然后在服务器端使用其内容）
- 发送（按原样读取和发送给客户端，无需解决方法）
- 更新（来自我从某处获得的内容）
LO表示使用大型对象存储文件，
列表示存储文件作为文本列（标准方式）。
让我们说'我'有''文件'' - 更小（Kb的十分） - 让我们说一些
JPG图片（啤酒照片:)该文件被''readed''原样传递给客户
（浏览器，app，等等）。在这种情况下，最好将
文件存储为普通列，因为它更容易访问它。这是
典型用途之一。每次我写这个图像，SQL解析器都需要解析
所有数据，分配适当的内存，....我认为，真正的不同之处不会是
很大，因为图片只有Kb的十分之一。
但是让我们有相同的图片，但是打印的分辨率非常高。
文件大小将是兆字节甚至更多[让我们说10Mb]。当然，由于数据完整性，
文件应该存储在DB中。在这种情况下，我们将在读取/更新时需要相当多的内存。每时每刻。如果我将
得到许多类似的请求 - 他们将直接转到DB，而不是抛出sime
''本地缓存''，它可能引发问题。 />
使用''text''列作为binaru文件事实[我是对的???]：
- SQL解析器需要解析的时间语句将是
显着（足够大小）
- SQL解析器所需的内存至少等于文件大小
- 同时获取数据，文件将在发送时存储在内存中
结果
- 当我收到结果时，PG将''释放''sql结果内存，
但文件保留在''my''侧（仍在吃内存）
- 更新文件时，我们至少需要2 * file_size内存（一个用于我的
生成SQLstatement，第二个用于PgSql内部进程 - 至少）
- 使用char / text类型不是''二进制安全''，所以，我需要转换它
（下一个额外的内存/ CPU）
/>
另一方面，LO事实[我是对的???]：
- 数据到文件不会抛出SQL解析器（没有额外的CPU / TIME
要求）
- PG使用pg_largeobjects表来直接存储文件'
- 数据以块的形式传输 - 没有nned for memory
- 因为二进制安全函数，不需要额外的转换
- 当我更新文件时，它不需要在内存中 - 可以是
从本地存储文件复制抛出缓冲区而PG也不需要
将其内容存储在内存中（我不计算缓冲区/ tupple /。 ..）
请告诉我，我在哪里，哪里是假的。
如果最后的PG使用LO抛出标准SQL，然后我写的是
false，并且没有理由使用LO。然后我会想念那个
的特征.....所以，我希望它按照假设的方式工作:)
所以，我的问题：
1.在分析''megs''语句时，解析器的声称有多大？我与$ LO相比，
是指CPU /内存？
2. PG如何在内部使用LO - 直接抛出SQL语句？
3当我访问LO文件时，它将被真正访问''缓冲区''
或PgSql以某种方式准备文件''原样'然后访问它？
如果事情按照我的假设工作（但我现在不知道在哪里验证，
除了源和邮件列表，所以，我选择更快的方式：）< br $> b $ b结论：
a。对于较小的文件，CPU / MEMORY使用之间的差异很小
LO：这种情况并不真正需要
列：胜利，因为一致/可移植的工作
b。文件，其内容需要''原样''（全尺寸）和
它们在服务器端它们也没有太大的区别
LO：这里的文件可能会节省额外的CPU，但只有在
更新内容时
列：胜利，CPU差异仅在更新时
c。较大的文件，不在服务器端处理（通常发送到客户端）
LO：获胜，在读取时保存一个内存，还有内存
和更新时的CPU时间
专栏：丢失，东方冗余CPU& MEMORY，缺点
LO是lees然后节省了资源
d。更大的文件，在服务器@本地fs上处理 - 比如[c]
我希望新的PgSql 7.4文档（据我所知它还没有发布）
在LO中包含关于此的小章节/段落。如果某人不熟悉PG
（和我一样），那么就没有足够的信息''使用''LO。
感谢您的更正和评论关于LO /列二进制存储。
我希望这封电子邮件属于一般性讨论。如果没有，我很抱歉让我知道 - 我会转发。
祝你好运，
拉达''雷''Lostak
Unreal64开发小组
http：// www。 orcave.com
http://www.unreal64.net
------------------------------------------ --------------------------------
在20世纪60年代，你需要两个C64的强大功能得到一枚火箭
到月球。现在你需要一台数量庞大的机器，以便运行最流行的GUI，实现更强大的功能。
-------- -------------------（广播结束）-------------------------- -
提示6：您是否搜索了我们的列表档案？
http://archives.postgresql.org
Hi there :)
Someone asks about performance of Large objects [LO] in PgSql [PG]. It
was interesting for me, because I didn''t work yet with them yet and I will
have to soon. I tried search the web, doc and mailinglists, but I didn''t
found any adequate reply. I would be happy, of someone, who have experimence
with them (or who know PG internals :) can make it clean. I think I am not
alone, who is interested in this topic.
In past time, where row data was limited by single tupple (8K-32K), LO
was really needed, if you wanted to store more than 8K per row. Older
implementation issues are described in documentation.
Today, there is 1G limit per row. Is there reason to use LO instead of
storing simple ''text'' into database table ? (let''s ignore 2G limit on LO,
which can make LO interesting in some cases :)Documentation says it is
obsolvete. But documentation also doesn''t tell anything about performance,
suggestion about using, etc.
By ''access'' to binary file, I understand:
- reading (and then working with its contents on server side)
- sending (read and send as-is to client, without workaround)
- updating (from contents which I get from somewhere)
LO means storing file using large objects,
column means storing file as ''text'' column (standard way).
Let''s say I am having ''file'' - smaller (tenhts of Kb) - let''s say some
JPG image (photo of beer :) The file is ''readed'' as is and passed to client
(browser, app, whatever). In this case, would be probably better to store
file as normal column, because it is easier to access it. This is one of
typical use. Every time I will write to that image, SQL parser need to parse
all datas, allocate proper memory, .... I think, real differnece will not be
too big, since picture having just tenths of Kb.
But let''s have the same picture, but in pretty fine resolution for
printing. File size will be megabytes and more [let''s say 10Mb]. Ofcourse,
file should be stored in DB because of data integrity. In this case, we will
need pretty much memory while reading/updating. All the time. And if I will
get many similar requests - and they will go directly to DB, not throw sime
''local cache'', it can raise problem.
Using ''text'' column as binaru file facts [Am I right ???]:
- time which SQL parser need to parse statements will be
significant (adequate its size)
- memory which SQL parser need will be at least equal to filesize
- while fetching datas, file will be stored in memory while sending
result
- when I will receive result, PG will ''release'' sql result memory,
but file stay on ''my'' side (still eating memory)
- while updating file, we need 2*file_size memory at least (one for my
generated SQLstatement, second for PgSql internal process - at least)
- using char/text type is not ''binary safe'', so, I need to convert it
(next additional memory/CPU)
On the other side, LO facts [Am I right ???]:
- datas to file are not going throw SQL parser (no additional CPU/TIME
requirements)
- PG uses pg_largeobjects table to store the files ''directly''
- datas are transfered in blocks - there is no nned for memory
- because of binary safe functions, no additional converts needed
- when I am updatinng the file, it doesn''t need to be in memory - can be
copied throw buffer from local stored file and PG also doesn''t need to
have its contents in memory (I don''t count buffer/tupple/...)
Please, let me know, where I am true and where I am false.
If the PG at the end uses LO throw standard SQL, then everigth I wrote is
false and there is really no reason for use LO. Then I will miss that
feature..... So, I hope it works the assumed way :)
So, my questions:
1. How big are claims of parser while analyzing ''megs'' statement ? I
mean CPU/Memory in comparing with LO ?
2. How PG works with LO internally - throw SQL statements or directly ?
3. When I will access LO file, it will be accessed really thow ''buffers''
or PgSql somehow prepare file ''as-is'' and then access it ?
If the things works as I assume (but I don''t know where to verify right now,
except sources and mailing list, so, I selected faster way :)
Conclusion:
a. For smaller files the difference between CPU/MEMORY usage is small
LO: is not really needed for this case
column: wins, because of consistent/portable work
b. Files, which contents are needed ''as is'' (full size) and work with
them on server side it is also not too big difference
LO: files here will probably save additional CPU, but only while
updating contents
column: wins, CPU difference only while updating
c. Larger files, not processed on server-side (typically sent to client)
LO: wins, save a LOTT OF memory while reading and also memory
and cpu time while updating
column: lost, easts redundant CPU & MEMORY, disadvantages of
LO are lees then saved resources
d. Larger files, processed on server @ local fs - like [c]
I wish new PgSql 7.4 documentation (as far I know it was not yet released)
contain small chapter / paragraph at LO about this. If someone is new to PG
(like me), there is no enough information about ''using'' LO.
Thank you for corrections and comments about LO/columns binary storing.
I hope this email belong to general discussion. If no, I am sorry and let me
know - I will repost.
Best regards,
Lada ''Ray'' Lostak
Unreal64 Develop group
http://www.orcave.com
http://www.unreal64.net
--------------------------------------------------------------------------
In the 1960s you needed the power of two C64s to get a rocket
to the moon. Now you need a machine which is a vast number
of times more powerful just to run the most popular GUI.
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
>有人询问PgSql [PG]中大对象[LO]的性能。它
> Someone asks about performance of Large objects [LO] in PgSql [PG]. It
对我来说很有意思，因为我还没有和他们一起工作，我很快就会这样做。我尝试搜索网络，文档和邮件列表，但我没有找到任何适当的答复。对于那些对他们有实验经验的人（或者知道PG内部人员:)可以让它变得干净，我会很高兴。我认为我并不孤单，谁对这个话题感兴趣。
你当然不是一个人。我还没有做到这一点，但是在不久的将来需要通过基于网络的数据库项目获得
我正处于
计划中这个时刻将涉及允许用户上传PNG，GIF
和JPeG图像，并将它们显示在某种画册中。类型
布局。
在过去的时间里，行数据受到单个tupple（8K-32K）的限制，真的需要LO
，如果你想每行存储超过8K。文档中描述了较旧的
实现问题。
我有点担心这个，但不是因为大对象支持
（我理解这是通过不存储对象数据来处理的
直接在行中，因此完全绕过这个限制），而不是
我认为大行的可伸缩性有很多列
总计超过32,768字节。
今天，每行有1G限制。是否有理由使用LO而不是将简单的文本存储到数据库表中？ （让我们忽略2G限制
这取决于你的应用程序的需求。如果你要存储的全部
是文本数据，那么你的选择是你的。如果你要存储二进制数据，那么简单的文字可能就不合适了。
LO，这可以使LO变得有趣案例:)文件说它是阻碍的。但是文档也没有说明
性能，使用建议等等。
通过访问二进制文件，我明白了：
- 阅读（然后在服务器端使用其内容）
- 发送（按原样读取和发送给客户端，无需解决方法）
- 更新（从我从某处获得的内容）
LO表示使用大对象存储文件，
列表示将文件存储为文本列（标准方式）。
我不知道这个。希望在这个区域有经验的其他人会跳到这里并清楚这件事。
让我们说我有''档案'' - 更小（Kb的十分之一） - 让我们说一些
JPG图片（啤酒照片:)该文件被''readed''原样传递给
客户端（浏览器，app，等等）。在这种情况下，将文件存储为普通列可能会更好，因为它更容易访问它。这是典型的用途之一。每次我写这个图像，SQL解析器需要解析所有数据，分配适当的内存，....我认为，真正的
差异不会太大，因为图片只有十分之一的KB。
如果你可以确保图像大小永远不会超过你的专栏的宽度，那么你会没事的，但请注意图像大小可以变化很大
取决于除图像尺寸之外的许多因素，
之一经常被忽视的是设计不佳的可能性
$ b用于生成JPeG图像的应用程序中的$ b压缩算法
或用户选择最小压缩因为他们想要更好的
质量图像（我有时会在网络上这样做）开发我这样做，但只有在适当的时候才能获得
。
但是，请记住，将二进制数据存储在用于
文本可能会导致一些问题（即使现在还没有），因为
它将包含意外数据。您可能应该查看文本
编码系统，看看是否存在一些潜在的问题
，例如，您的数据被误解为中文文本。 br />
但是让我们有相同的图片，但是打印的分辨率相当高。文件大小将是兆字节甚至更多[让我们说10Mb]。
当然，由于数据完整性，文件应该存储在DB中。在这种情况下，我们在阅读/更新时需要相当多的内存。所有的时间。如果我得到许多类似的请求 - 他们将直接转到DB，而不是抛出sime''本地缓存''，它可能会引发问题。
如果您不能信任您的基础文件系统来可靠地存储文件
，那么您真的不应该存储数据库。
如果你担心事务的交易方面，那么你的担心肯定是值得保证的。
[sNip - 其他问题我不知道答案，但是希望]我希望新的PgSql 7.4文档（据我所知它还没有发布）在LO中包含小章节/段落对这个。如果某人是PG的新手（就像我一样），那么就没有足够的信息来''使用''LO。
我也想看到这个。
感谢您对LO /列二进制存储的更正和评论。
was interesting for me, because I didn''t work yet with them yet and I
will have to soon. I tried search the web, doc and mailinglists, but I
didn''t found any adequate reply. I would be happy, of someone, who have
experimence with them (or who know PG internals :) can make it clean. I
think I am not alone, who is interested in this topic.
You''re certainly not alone. I haven''t done this yet, but will need to
in the near future with a web-based database project I''m in the midst of
planning at the moment which will involve allowing users to upload PNG, GIF
and JPeG images and having them displayed in a sort of "picture album" type
layout.
In past time, where row data was limited by single tupple (8K-32K), LO
was really needed, if you wanted to store more than 8K per row. Older
implementation issues are described in documentation.
I''m somewhat concerned about this, but not because of the large object
support (which I understand is handled by not storing the object data
directly in the row, thus circumventing this limitation altogether), rather
I think about scalability with large rows that have a lot of columns that
sum up to more than 32,768 bytes in size.
Today, there is 1G limit per row. Is there reason to use LO instead of
storing simple ''text'' into database table ? (let''s ignore 2G limit on
That depends on the needs of your application. If all you''re storing
is text data, then the choice is yours. If you''re storing binary data,
then simple text probably won''t be appropriate.
LO, which can make LO interesting in some cases :)Documentation says it
is obsolvete. But documentation also doesn''t tell anything about
performance, suggestion about using, etc.
By ''access'' to binary file, I understand:
- reading (and then working with its contents on server side)
- sending (read and send as-is to client, without workaround)
- updating (from contents which I get from somewhere)
LO means storing file using large objects,
column means storing file as ''text'' column (standard way).
I don''t know about this. Hopefully someone else who has experience in
this area will jump in here and clear this matter up.
Let''s say I am having ''file'' - smaller (tenhts of Kb) - let''s say some
JPG image (photo of beer :) The file is ''readed'' as is and passed to
client (browser, app, whatever). In this case, would be probably better
to store file as normal column, because it is easier to access it. This
is one of typical use. Every time I will write to that image, SQL parser
need to parse all datas, allocate proper memory, .... I think, real
differnece will not be too big, since picture having just tenths of Kb.
If you can ensure that the image size will never exceed the width of
your column, then you''ll be okay, but note that image sizes can vary widely
depending on a number of factors in addition to image dimensions, one of
which that is often overlooked is the possibility of poorly designed
compression algorithms in the applications used to generate the JPeG image
or the user selecting minimal compression because they want a better
quality image (I do this sometimes in the web development I do, but only
when it''s appropriate).
Remember, however, that storing binary data in a column intended for
text might cause some problems down the road (even if it doesn''t now) since
it will contain unexpected data. You probably should look into the text
encoding systems out there to see if there could be some potential problems
with, for example, your data being misinterpreted as Chinese text.
But let''s have the same picture, but in pretty fine resolution for
printing. File size will be megabytes and more [let''s say 10Mb].
Ofcourse, file should be stored in DB because of data integrity. In this
case, we will need pretty much memory while reading/updating. All the
time. And if I will get many similar requests - and they will go
directly to DB, not throw sime ''local cache'', it can raise problem.
If you can''t trust your underlying file system to store files
reliably, then you really shouldn''t store your database their either.
If you''re concerned about the transactional side of things, then your
concern is definitely warranted.
[sNip -- other questions I don''t know the answers for, but would like to] I wish new PgSql 7.4 documentation (as far I know it was not yet
released) contain small chapter / paragraph at LO about this. If someone
is new to PG (like me), there is no enough information about ''using'' LO.
I''d like to see this too.
Thank you for corrections and comments about LO/columns binary storing.
[sNip]
感谢您的提问。我本来想要问一些这个
我自己，但你给我省了麻烦。 =）
-
Randolf Richardson - rr@8x.ca
Inter-Corporate Computer&网络服务公司
加拿大不列颠哥伦比亚省温哥华
http://www.8x.ca/
此消息来自安全，可靠，价值高达b $ b的高性能网络。一个Novell NetWare网络。
[sNip]
Thanks for asking. I''ve been meaning to ask about some of this
myself, but you''ve saved me the trouble. =)
--
Randolf Richardson - rr@8x.ca
Inter-Corporate Computer & Network Services, Inc.
Vancouver, British Columbia, Canada
http://www.8x.ca/
This message originated from within a secure, reliable,
high-performance network ... a Novell NetWare network.
这篇关于数组迭代？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
