Re: FECTH PRIOR em PL/PGSQL

View: New views
1 Messages — Rating Filter:   Alert me  

Parent Message unknown Re: FECTH PRIOR em PL/PGSQL

by António Cascalheira :: Rate this Message:

Reply to Author | View Threaded | Show Only this Message

Some parts of this message have been removed. Learn more about Nabble's security policy.
Viva!

Estou   a utilizar curosres em Postgresql já à algum tempo e sem problemas (A ferramenta que estou a utilizar é o Realbasic).  As funções NEXT, PRIOR, FIRST e LAST funcionam sem problemas.

Algum código de exemplo:

// Para declarar o cursor:
DECLARE cursor_clientes SCROLL CURSOR WITH HOLD FOR SELECT * FROM gc_clientes ORDER BY n_cliente;

// Para ir para o ultimo registo:
FETCH LAST FROM cursor_clientes;

// Para ir para o primeiro registo:
FETCH FIRST FROM cursor_clientes;

// Para ir para o registo seguinte:
FETCH NEXT FROM cursor_clientes;

// Para ir para o registo anterior:
FETCH PRIOR FROM cursor_clientes;

Este código têm funcionado sem qualquer problema,  mas sendo este codigo de um programa muito simples, eu não estou a utilizar FUNCTION e não sei se o problema no seu caso não poderá ser do modo como está a usar.

Atentamente

A. Cascalheira



On Mar 26, 2007, at 1:35 PM, Walter Cruz wrote:

Se eu não estou enganado, alguém estava trabalhando em cursores bi-direcionais na pg-hackers.

[]'s
- Walter

On 3/3/07, Rodrigo Hjort <rodrigo.hjort@...> wrote:
A instrução FETCH tem sintaxes bem distintas em SQL [1] e dentro da PL/pgSQL [2]. Eis um exemplo de código.

-- esta função retorna um cursor ...
CREATE OR REPLACE FUNCTION listar_pessoa(ref refcursor)
  RETURNS refcursor AS $$
BEGIN
  OPEN ref FOR
    SELECT id, nome FROM pessoa ORDER by id;
  RETURN ref;
END
$$ LANGUAGE plpgsql;

-- ... que pode ser facilmente navegado com instruções SQL de dentro de uma mesma transação
BEGIN;
SELECT listar_pessoa('cur1');
FETCH NEXT FROM "cur1";
FETCH NEXT FROM "cur1";
FETCH PRIOR FROM "cur1";
CLOSE "cur1";
END;

-- aparentemente não é possível navegar num cursor para outra direção senão para frente e de 1 em 1 de dentro da PL/pgSQL
CREATE OR REPLACE FUNCTION listar_inverso_pessoa(ref refcursor)
  RETURNS SETOF pessoa AS $$
DECLARE
  rec record;
BEGIN
--  FETCH NEXT FROM ref; -- causa um erro de compilação
  FETCH ref INTO rec;
  RETURN NEXT rec;
  RETURN;
END
$$ LANGUAGE plpgsql;

-- sendo assim, resta fazer as instruções por fora mesmo
BEGIN;
SELECT listar_pessoa('cur2');
FETCH LAST FROM "cur2";
FETCH PRIOR FROM "cur2";
SELECT * FROM listar_inverso_pessoa('cur2');
END;

Mas se você estiver processando diversos registros em uma função em PL/pgSQL e precisar voltar um registro, pode sempre armazenar o ponteiro (record) da última linha. Talvez resolva o seu problema.

Ou senão, melhor ainda, utilize os códigos-fontes do PostgreSQL e implemente essa funcionalidade em PL/pgSQL! :)

[1] FETCH [ direction { FROM | IN } ] cursorname
http://www.postgresql.org/docs/8.2/interactive/sql- fetch.html

[2] FETCH cursor INTO target
http://www.postgresql.org/docs/8.2/interactive/plpgsql-cursors.html#PLPGSQL-CURSOR-USING

--
Atenciosamente,

Rodrigo Hjort
Icewall Tecnologias
http://www.icewall.com.br


2007/2/24, Alan <alan.postgres@...>:
Olá pessoal,

Após varias tentativas, sem sucesso, de usar FECTH PRIOR em uma função PL/PGSQL.
Peço a ajuda da lista, para alguma dica ou exemplo de uma função que execute um " FETCH PRIOR FROM cursor" em uma função PL/PGSQL.

Desde já, agradeço pela atenção.


_______________________________________________
Grupo de Usuários do PostgreSQL no Brasil
Antes de perguntar consulte o manual
http://pgdocptbr.sourceforge.net/

Para editar suas opções ou sair da lista acesse a página da lista em:
http://pgfoundry.org/mailman/listinfo/brasil-usuarios

_______________________________________________
Grupo de Usuários do PostgreSQL no Brasil
Antes de perguntar consulte o manual

Para editar suas opções ou sair da lista acesse a página da lista em:

************************************
Here is my Visit Card:



BEGIN:VCARD
VERSION:3.0
N:Cascalheira;António;;;
FN:António Cascalheira
ORG:OC Soft;
EMAIL;type=INTERNET;type=HOME;type=pref:cascalheira@gmail.com
TEL;type=CELL;type=pref:960051393
X-MSN;type=HOME;type=pref:cascalheira81@hotmail.com
X-YAHOO;type=HOME;type=pref:cascalheira@yahoo.com
X-ICQ;type=HOME;type=pref:13254272
PHOTO;BASE64:
TU0AKgAAFsKAGJQiI6LUOHRaQdZh05ws3LAOQ0OoFgCA4LAPF1Phk5rYPntdB8+r0PINhB9EMUPo
FgyVgh1IskRoCTo9mCAspYMElHBg0K0OnFZB40xA4LMP0KkrANllMhken0KlNMhouKMNGBThoyKk
NmdWhw3UM4R6yh83x43UgyqwNmBShkspsMjk8hYYnAJlqNmtZQxaiYlooKlxQBK4BIyqoJGhXBS2
hQ3LEKHddhY9r4LGFSBQsp8InRcBU85c8Zc5rcLG5aBXU6Rea5cBY4rwLGlWBMUm4GE1LBAuKQI8
EImBUhIsqEJGJShIjIkHD9Bg8mpQIE1LhEopoJFJOBIpp3hqQJF5ThIxq4Jl9VhMyKwKFFMhEdoA
HDc9A4hIUH8QyleCgxlWyRaA2NSthyPgGi4UIJjM3L3gojoLD6zQ/GACY/l+CqQAsKZMAkLblLGC
g9l6Cw7l1DkTjo1SHskWQKje1pCmKCwpEgCIUDaBgnkyCYqk8CMggiKhOAiLrDh0ProkO/RGgeHx
EAgHZBggIhGAeJZLAkJpMAiJz5yIKhOggIRDgeEIwgWGI5AaMUBjg2Y1soLzyC6UoIp+DhEGQEAt
E0CIgEIB4xuONUAQq2AKkGYNGGCCxCmGC0ZgqM71K6CY0Pg1ILDgWoLDkWwLjaygzvhSo9RORZkg
uJhFggFY5AcI5Igi7AIigTIJCUR4IBwPYGiTWwkEk+j+BrBQckEBweEQB4iEgCFaggGA6gcDwtgW
HQ8gZIwIjOygtuaMMBtYCo0leCowuOxYOECYQQD02wej+B4kEYBw1leCUJjlUA7tnVQLD8X4LVKC
o6l0CzSgsMz21KCg2tbiYKjRAAvlOCYxFSCYxvgNxatc1oSjGBYYDuBogyyJzs0GBwZDwBolkoCL
vSKTwICo0An0CIdfBbNwPi6BQSC+BYjEMCA03E44uuO4wJC0UYJXYCQyFcCo5FwC6EA6Q6TjyzSy
greoGibW2Hsa9Q+NtCoLXmCwyFUCaigrio9ttF7I4rpgKC5PItOELxSvW49PgqOJYAmEAwAWG5AS
jpQZDsBocQUKBNZ1IQrwaKEyh/Z8PASDAngSJUpjNAA34XAT4k4CYrsOLBQAi8wI9kB+rDQWALDs
XAPj0XYPwmPmDDOxgajuBgmEmCFM0MCQ4RiOrZi8UYKK0Cgz3UODW02CjmAp6oL3Pqwuzu5uMgm4
wJjY1oikICAOW0Gg9AaFA1AYH7+CkTwEwlnZWSA4DAUQEgeCuAoIZ0wwCqAoGk1oVjDuZOWgMKaR
0wgSCqaB2iSE8oiAmVcCRDwOCAF6B8QQwALBzYEpALiQgYhxAaE86yQQIGJIeBUPrrWoCpAoY4Cp
Y27tZMmBVT7Dl1MZAkngCIXzjscAmYsCZlgLA2DgA4C4TgEgqN4EVKAOV7AiccBp0wNQ3gNcIBMN
wtzCuFCYJYCIRxHgRi8A8JLzjkngO4mQCQWBQpDZzE4CQXxSATDCKUDEJgPPCYSwsOJqhDDGAuFV
5wKzeBXZyFZnIZWOh5YWG8WIFTEqnAoogCgejNB1FsBUMqAw1IxYuBQL6eTiHEcIBFgIFg1m5BEG
IBYJwzgLBE0cCoTgEBBEEA8M4sAKBslW7QCUd2dJHQYBMJC0gciAAaEARADmaO4SEFU75VwIhadr
Ex9AEQ1itA3CYkRlw9i7AqhpDiGw+KOCQdB+wDQonZmq+gxSAwux/k0BBjgFF/gWEBCoN4sm/ijA
iGOBzc2NoDgbCE4Sm0HN0WAA0EQWwFAvDWAxPBijHrjAk7ICIU1AhIVsy1myYmchATQDVYIQxFgP
Cil8LAnwJhZMPQCESDAIldA3J4D6KaFQqD4L5rTC05AWD+MACoNQ6gNB5Mk3ytzfl8SGJwCASlbT
mAiGI85HQKh3NsG2ZgVqwGfSQneQtAAuijAmFRmoNnlhMEeA54QFA8Gjk4BQLyDY8wbTAJcCEFzg
HkmgrgHCTIuAMB0H8BoQxGHXN/Jo4px6JASobIt4cqgLItArX96qoTVB3RO3kCoMA4ANBmHcB0/U
wG/euBQKigQlCSAhXVq7dJYhxFspRUBiQquanEBI7Zw0GhNSgDYOwCwpG/XZB6soqlwHqDEbmWIa
jKJkq5WESatzsrDAiDMPIDQVhxAY0EBlNrLiKAgE6OENgyCnAy8ID9qa0KVYreCtJlxDjJAsGOuz
JQFg8OmE28oUEQVAO6iAJlvk7RLkKGEU8DxYLow6XyDQmgKW9VusYIahAZhyAYFI7LN0iUADC3R1
TFpQh0kdcVEQElvnyAiEFWANw+gPBbe4F6bgXhzcsH8BwO5k00AeFYp4cxaAdKKZJUCogLxHXmBV
goFA+obEQMoC4bBUgRKaAoIgiUtM1jrOJmyXzqgQCuaB7VwIRFanKYcLJ4gcXrBWGwBgNw8AOpYB
A7YEGbnBaq3RkJq1QXE0cBaQTt7PhUEyr/IIKw3gOBVoDI98Vug4XtNdJAoQNkgA+95dCAMqqiNW
ZQObIm4h2YWHpgwZmqQxAYENZ4RBFJaWNiSrePD5R9NBXW51zEv27AkEQQgDgXG8CUIsB19lciYv
rtg8NjQI6tjYHQy4djYhsZEeZENEGppDE6BIH6hAYh1AYjsBgKg3gMsiA0HogwGhOEmBlRoHhBMG
D4bMOJrQ+C7AuH42wdjVBvyuaPL4FhIjOAuEFJgHgvALzTHZXwQlYBBvoELNQSxJgTCfYrCU1QsH
iCzWAHgfQGgwDiA7FgEM3X2AhN8J22HBRNOOUUCeNm4VO6EGhGMywKtTuYoEKR88mAOBPSPIoDrZ
725gEoRwGWwAfUbDo2NrhAMGerEE1qlVOottKbMQykwiWWA4FoBe+QH29rCsQRtmtlbK3Vc6u6Xy
on3W6EsSQD6YeFsUE9L6uoPASdUBQNZrQ8Nu7EZfx4FQxGMC0KECFnOTqCzU5TmIcwHWRAeEEQoF
iWAgUUae05tpGcFcSyJ36lJQlCMqbMQYwwLiEGGBQFIYwFAd7hTgB4UMWq75xJgCAWWc3JdwkcKp
1gZ5IBq/emCuAmXneczfOdEW6LlMguqR7cDYh2MuaJg8oQ1TM3N8YCAPhCgOfsAwGegwY20WAjIW
BDLi+w9mD+qcnoEAM0D0RUoaiCMoDqNUNOAs9yAqEeGWAojMAWAuCkAS5Ep0S+tsCsTK+UCq0uCo
zgaAN4B0D4Acmk+yAkgCAiCMEirCZqg6DCFQAiDMMeXSRkVA1oNWNaleAoQeAqoo8yAgCYZqCCm6
B+EOAaBiDs3ipG9MAucUA2tID+NilA8cMe7AGAsARUU6U6YanioUYMEKGIQoNiBUl8A6CyAUCURy
p2AirdDePE5XDeUCB+WYTaAY34vGWKAiB8ESSo/iB+pyR8nKOEaiDIXNB4lCYQY+MgPgoAokeyh8
CeN+SwAeB0m0Bg9EB4D+rSFqA6DUMeUbAaqYNG4Oy4Ni1iUoRjAODglCoaAoDolWDqZERMAs94As
BGgSA8CyAWWG0MS+pZDi0QjgBoeWB2SYq2pcAijeAk48AeB2UIB4aUZ+SKPEeiasQmYrBqXWborI
K0MUNywEC2ogSwyWEGAcBe3gB8ECUiGCBAcUQkVA1UJHFIAqEKGCAoEWGKAvAHDLFuF+AwD0tWNm
4YRQNUbeMsAqA8CmgPF6jqZo0QRACQEU6pCYCGSdEoSujmdCSizUBqyCCMRyCI7u87HAY4OWagbo
nI+UpKnMOAFGAg1UmWAoxiAmycN2AYByD2AsDwFuA8DkRikIAkDkNaDyNnH9AUYSjZDA7Q7Q1cDi
VBFkhW7KMoDyNiDiMoAyi0A2CsAUCAvoCSRyCAOmBcDgAYCESm8QpWUCCMjmSkAeBwECAeWWm8Zq
6aAiwkDBBnA4S0ecMMbWlkTysYsYeitMI6AopYAkgGBmDtCgKQYQheAisHKKQ4YW1iiCmY9gDaeo
uKlzFUQ6IK9o/0h0F8AuPeAmyWAOA87hCMAcr0fwf0rFGYEuS6OywgAlLBGgmSBwcjLAAaCsNAC2
NAC8qCOUk4tAVBM4bkbpOCAjIjJQD6Ni66luCAOgBkDmcSFkA+lIDeXUDcQADaNysKAmC2MOlmaq
POooYe8cXUfArqAnEgMYlae2QG9YCsN+/eAOAqCaASBETW07LQ1+jvGYjgCQ5KdCAgvUAayAWhJK
sUeiDWmYVKAlQlMEMbM0uLBqxG+20uqVAKAq+6CSWMB2D+hWFqA+UQNcuKhyemAqC+rs5WfYkKDA
OUDEPIC4NAM4kGOFPMRCMOMWuCh+QHO8AqECUmdQAeAWB2AMl6AWmQAe14AeCESyCIV8CG7ux8Ae
BuyUBc9EB6P5CIAip6oiOOMcAmwEnS8uAk6FNQpgCOWkToAo9gvE8UCSEeJ8FYA4fbPosHHAW+Cu
E6L2MPOGoiPODOlcayUMfCFOqLBnQlAQNcVBKOAsEYGGAxKCAoBiDmAYBe3qBy5g5IAhQJTA9KzV
Lc/eTQ/uAaBc3g48s0AgREAivAgeXUmhOCAlM4h+mYCoSEpcoM++w3V60MPm5OAyDAFIA2q9JYSO
t2zkkySEnNR+MQOaopKCrSFua6FqAuykAsliMcgeMeX2Ao4GNoPgB2QUhiAa3aAdD8P0pzSoWmWl
S1LlNeSZS5XaEOAgB+OgeaAhEid4M8OUV0U0mY/MAvQ2W+nScIx0ZzWG34AwoHK63XA4huQbRub/
OEqCaoDGPODWMeiKIKNINVRdO9PqQCgdPoDMboDemYDANABo3gfzLQzUB42eB4P5SwAfEICSZqB2
TQBoSYBecrVK7oCKV8CqPEoA3SjeAjVlB6b9JYNA0tD6UJE1QYDuAoC2E8Ka2OPJUKrMMaPaeQaq
kKC+j/PAApFhNElUAvTkNwYkQBXAXTUYaqaoCgt8mAAWBQpHGWCUec+yjiZqCaV2CqMOCLBdXrVW
psAfLcAjJIOoN/cCj9Ns2vLyOUzcCAEKAgBcjOBSDSAWBAaPDsAeQeWza4SJGEzdEoP6j+DIObPp
Cs/8NVMqiLTla3LYOKY0okkMPPR2CKTQA0CwAUBkquOSkMQGC2Y0CuPIc6SGdqc6msvKB0UIZiAd
VLZtXfQdVGWNOsAeBSDWAbK6ASA+C4AVXVLIAeguXAocDk1qF/dEFGA2TIsWc1R8CoN++M2Ss+VO
PcbolNbpPpXBPolIfBWQdiUCCgec10RuASB0ECAcgkAiDclWDiNGDONaf8Akc/TGcKZ22aRyB0EF
X2WeB9IrVAAeksAYWyAUAmCOAQAsCWAQBqyQCk2wDeNGDUZEDEPU8bZWQdT1Yk+62RLwsKxCAgg6
UuQcPabUiVTODSMfEQsIPOC8boaimq+YvrBcBJDQ7hTgzkOUC/f+uOMeCcPFjMPANATCAiB4TQBW
tiA2gTSnP2i0wVXQ2qVsPYAmDOocDQlC0afeAqoAMSSNCGEiAxA4A3MUg6vE0ocKQZPTPTd6akj+
aslbHDZQAomgW+86Cat8BwD0AYA0CqASBIZNJEAhSxX2zVN6AfVWBaDaAaBAaIBAW0T+AUizfJF6
BODFJ0DsAcmgDSocDcqgetG9EMbo6RJRYZf0nEAgCMEWAuCI9PDc8VVwPeYsMfJsMcakNA8zTKMQ
OOjSTsAopKClLaV8jqCGzVXUAyCoASAWBoAOAgB4APniAOAOBUALnyAKASBiAKAkByAOA3hkBsxU
CiWNOGauNyDSuKDWYW6NkFnFd8MYjTWGhse0iglut3LZX+FMAwBeDUAoBbLPnOAgcIApEQAnQlPi
PIjzikfRpaQdZARjEiSIubCJlSWe/qAcBMmCBCaOBU+AB6eWCUfkCevoCyRAvzB9XEVAPSAmCsrs
C8Y6Z2kAdwapJoCycGOO0VWlnOS6N+jybsMipKoADULCQOA4BMZMBsW6q3WkdupKKup+OUOCApOd
JtQ3YZnOAjQPBYRzLESoQUBMDKAVlgAYsKNCFuAmDqnkVElkOOj8AnTFchJIAgCAVgCKRy52AkCO
vLZ6OGFKfCPhiqPWgcN8Su7uswjst88vpWNa8bTklUA+D8XifAAwCgASBKDMAUp2Ag3Tm+OJHI8W
oKg7uInSzmV4vLX5LoPtDxP+ceZSDEaoMMkBX+OPR9GXVQm5LoECV+PsBy31b+vMAllVBaVySO8a
awe2w68VtZu6jrTI4cuMd8FwA4QeA6DKlDY+Apl4AXlMAWzihsxzVvUIkKihHBkgrcAlsqpyB5Lm
BMDMAXWU7iciKoSKUCzcW+kJTGNAWptZZtS2yUBzLmmyAeB+aVsyAfcROfNq3Ma2iCVAQjr7RGmj
Bcx5JQ9gIkM+A20UDi4RY+AmAwi0jEAXtYmq+7fWuaSJeOhFY6AlRzDeOyCaSyBKDIAWBGl8CWSh
f0phR8xzVlBlGtce7ujkAfeyBtLnS5GgUJlRStOeN/ecQiLSAtB7jEruSFpytsSMAfSDKOA+DYFe
A2wuikEIRsDkFicZneOiAOyY8IOypg5JQKsWSPfW6aAgCgN+8EAgmQAcl7fOhlk8WmV8868RJhMm
FWPLooru3XZ1OsAhcYBgvWBkWDLgt6AlD4S8RCOaDayuYXmFa0OVyUrBGFGEC+FAK+FYA0C0E8OQ
ZyfakeAqEBHyBi+BXaAPb0/g7mt8jeAhLXpM/c34AgCLIqeUAYA4C4TZU5X9TuAg1Iphfos/V0u6
owmiWNLhZsWqeWBWyQBvBNVLlQWLiQdqbtzq0hte3NOc+dhtbsA2T3KGw0aqs8cO1kUgCeOhn8AK
mKASB2ECm2Ogjrf1I1LFBYEVCUvdeE7imTk6jh0mVr0oMSbsaYAqs+V1X21+B7p3BNjeAaBa9FJB
zQP4CESgphVwddEiiUPkOuedPIe4iMKRY6A1uQwzioTyjyairUlYSFmkAQAZnqBUvdCQAewqAjEp
OevKP26gmDwCCNIrb/GAtuvqvOWMczMnh6QAaidk2a7veoAcWWAegGBaDp0+DO3mTcBivWCM7ukw
auPbJRPhZVXERi/WAq/WA4oGAzpMsYrhm/YqrgDaFeQyGGAqX2AkA3heAQBaAKA4CwAXet3KAczi
B0WCA2pA11005yOtCIAn7gOwx06Yc0cAPcPgThsi5MUCB0P4BsciBiPz+cAcBaWuBUiupw+WaoDL
h6FdVyNaToMKOF9zCIAerIX2A32QA1DhR8w1nAMjBv8gOEVEAmEIGQAuBmDUAWAGA8AJ0bpIIADB
4gwaJjMDA+XwWRUKDionAiUEwECgmQgSkoECWlgkTUyESuoAkZFWFJIFDOrwqXFIFCuoQmR0kERu
gAeMDqDhqewaM54PEIDyAiY0lQeU04EC8pAiYlSEi6pAkU4sUUuESIjAgTEnSk+GS+pAvS6gpQkX
FGETBZjPJTIqgoX1LTVOES2oQiXrmfGAFSmjwgChgBQOJwKFCOCQoTwUIjCCyUiwhVojVyWkwiSY
yTauTUwESqnrypwkalgFDMrgoW5FUQmVk+ESCigeO4aOECDxSbwYKjeDRofAcQEQD8uEC0oJAnQg
Vk6D7lZ1GEiQkAgQkTxkiFjQqBGZFMGTSqg1YQ0ZFOGjSrQ2aPIZVSGTOpwwblaHDesA4clkHD+Y
QPLeDYNiIB4FBKBYPCuCQTDECYkkYCwqk2DYvFCDYsk6DImkkDApkwDLkgyMBTvkV4ODcWAOjcWQ
OjWWAPjA8IzFUDgsFADYlkiDIikYDAgEKC4YDmCgcj2CojEWC4pksDQvFHEZSgwMj4jCUoMyeDIs
k8DIjkYDInEcECAgAA0BAAADAAAAAQAwAAABAQADAAAAAQAwAAABAgADAAAAAwAAF2QBAwADAAAA
AQAFAAABBgADAAAAAQACAAABEQAEAAAAAQAAAAgBFQADAAAAAQADAAABFgAEAAAAAQAAAOMBFwAE
AAAAAQAAFroBGgAFAAAAAQAAF2oBGwAFAAAAAQAAF3IBHAADAAAAAQABAAABKAADAAAAAQACAAAA
AAAAAAgACAAIAAr8gAAAJxAACvyAAAAnEA==
X-ABUID:2E8782B5-5441-4080-BFD4-5EF9201B5396\:ABPerson
END:VCARD



************************************



_______________________________________________
Grupo de Usuários do PostgreSQL no Brasil
Antes de perguntar consulte o manual
http://pgdocptbr.sourceforge.net/

Para editar suas opções ou sair da lista acesse a página da lista em:
http://pgfoundry.org/mailman/listinfo/brasil-usuarios