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 < [EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]>:
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:[EMAIL PROTECTED]

TEL;type=CELL;type=pref:960051393

X-MSN;type=HOME;type=pref:[EMAIL PROTECTED]

X-YAHOO;type=HOME;type=pref:[EMAIL PROTECTED]

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

Responder a