From: Russell Rose | Passfield Data Systems <russellrose@passfield.co.uk>
To: "pgsql-sql@lists.postgresql.org" <pgsql-sql@lists.postgresql.org>
Subject: Get error when calling function that returns a table
Thread-Topic: Get error when calling function that returns a table
Thread-Index: Adp+yq9EJvdikkoZQc6tM/7iJt41SA==
Date: Mon, 25 Mar 2024 15:43:11 +0000
Message-ID: 
 <VI1P191MB0638AA48C8FA4AC3CC71B5B9F2362@VI1P191MB0638.EURP191.PROD.OUTLOOK.COM>
Accept-Language: en-GB, nl-NL, en-US
Content-Language: en-US
x-ms-exchange-antispam-messagedata-chunkcount: 1
x-ms-exchange-antispam-messagedata-0: 
 =?us-ascii?Q?ChJu9rDlvHldCystQGTHQ+AnmDTic7fpTL+vmE8rBDfsYDcptTIvkrNpbCf4?=
 =?us-ascii?Q?hXCupdddt+eAzFzbDm8HQleWjfTxSJy3Xx8P/DrN7fW/IbDjNCsviXGfjCXQ?=
 =?us-ascii?Q?A7ME8XG5lEHHk4sKR4XYiK+0oG3LZFl3hgS04AEJiITmlqkstV7GAB1pQvqn?=
 =?us-ascii?Q?Z/6zVwnjMD8kD9+NYAAsOL/iEdB3+38eKTyUCzP7ar3pp5tozRWZ2nhjuXa9?=
 =?us-ascii?Q?LyHg5Aq56k5cBBlZtDyDCjG6ae+ihbKnB7CugYhN9pUHEcsE3VaHSqBYUy9a?=
 =?us-ascii?Q?Qg3y8RLoX+1yxKRaKoQqm/64L+eKLodx/u4A6nyQyfZAFbgvFYA9ZvQCVgmb?=
 =?us-ascii?Q?xz8NV9ljab3kfM2sghWjTNVznI1T7srkYBF7+nh42+nPZDWxAFk6ljMcjpCd?=
 =?us-ascii?Q?gEVLFM7dU4jVuUdJXqYOtpPuEDWJCSoifEkjgSrVRzUKFOCbLMGUzeCuDMyW?=
 =?us-ascii?Q?6yePOZsene0kjARKs/5ns8rUiTfspI8ICGhnIEUA1pY8wyXRo/ih5bPV7pSv?=
 =?us-ascii?Q?tqvdxjwXA7xbHcreSsv2dixFM3vvlg+GTRfZXsPd830jjcXSVEcheKHrJA/s?=
 =?us-ascii?Q?mMeXL4PujTd5gJY5RFgKqhysh0B9HBYJqoOT4xMGRTctKok1vOJ0VskQvOha?=
 =?us-ascii?Q?DaPD5Ogg0dXYSOLsHi7DM4A1pUmufTj6r2yMIWdyyTB7gWzSJXLQZsFf5zDE?=
 =?us-ascii?Q?FM7p1kraMuV5Mgg4Gbb58kvydyknW4H/23nfsoUzLN0nkA5qinxQDu7+vZSp?=
 =?us-ascii?Q?BDFCabbk3tyfNHoEmyDHsIJhXOjRMJbIcWUilEB+fkSD2EOSdYuw/RIbvpaH?=
 =?us-ascii?Q?mbRFXwwGQAXe95hxbAeuWkEEhP17BhD7hQWStLLy075AOxuWuAJTmKvCbri8?=
 =?us-ascii?Q?FGKUe5V40F+dKLRMvWxF1WqhYPVdT2SToFIJcYvZkuinQFZ3HKhw8mpj7XMy?=
 =?us-ascii?Q?tNOY0aF2J7HxZt7/1gcujklKNPrO2w6MFTI/AMrLst8OEBH9qWQVsyCyKgZ0?=
 =?us-ascii?Q?7jvM0XT8WDJrUV3aTgl6JIa0e+k6rPR+D/IzawQRdoQHitB5gqjwn4aVKWcL?=
 =?us-ascii?Q?2PDNE6bHlz9GzpfgpAD1iyzSV3Mk9/RkYXVJFhcjRcd45TwzFUFc+ytmh+7d?=
 =?us-ascii?Q?czZE2ziXLuLTvmYdg47f1iTC/Z+cs1vY1TD6qWVbQqYEUveTaxzz7oSutlo5?=
 =?us-ascii?Q?IEsaSPCdpWv9vrNiKDTyNB+jYr/a/QlnKX8MH64Wn0jrfPDI/XEoF7onpwDa?=
 =?us-ascii?Q?HOiqsV5zjAPOr+8i01jYwmsPaUS20QjBUIAa+jobn8jK5ECyKDRxX9bSgJdB?=
 =?us-ascii?Q?W1dGzf2VuHg0cqPUSNs/ffd+lP/KDJsvpqPiS2bElXJUX0k/Ehizv8bKlOJC?=
 =?us-ascii?Q?dIWxKF5J+ydsM0stV/LzMhVJ/LmIzUIWJ7Yt4gF+U/X3b8LK1UHr0d6AcQKo?=
 =?us-ascii?Q?vsyViwS7gkCiVY5aqCPJPYddI4Ll5bP1gTB4LCPJLmGJDhN0+8RGSdtJ9guq?=
 =?us-ascii?Q?gszc80sRQGUNKNolDz+909kdytcEb+81y/TRyOfosbFMG8tY6TGsVI7QeBLc?=
 =?us-ascii?Q?gCvRC1enmLe89D+MMMc=3D?=
Content-Type: multipart/mixed;
	boundary="_005_VI1P191MB0638AA48C8FA4AC3CC71B5B9F2362VI1P191MB0638EURP_"
MIME-Version: 1.0
X-OriginatorOrg: passfield.co.uk
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-AuthSource: VI1P191MB0638.EURP191.PROD.OUTLOOK.COM
X-MS-Exchange-CrossTenant-Network-Message-Id: 
 9fdca040-fcc8-4d6b-dadf-08dc4ce246c6
X-MS-Exchange-CrossTenant-originalarrivaltime: 25 Mar 2024 15:43:11.3036
 (UTC)
X-MS-Exchange-CrossTenant-fromentityheader: Hosted
X-MS-Exchange-CrossTenant-id: 581c1e5a-548f-43ab-a89b-dbf1fe593fd3
X-MS-Exchange-CrossTenant-mailboxtype: HOSTED
X-MS-Exchange-CrossTenant-userprincipalname: 
 w7CmZUcGvM+VKaMGcTF8Ph38OKVYFkUdUe5uoFPmPfK/qalL79Hyv2YY3CxxyuMA
X-MS-Exchange-Transport-CrossTenantHeadersStamped: VE1P191MB1005
List-Id: <pgsql-sql.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-sql@lists.postgresql.org>
List-Owner: <mailto:pgsql-sql-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-sql>
Archived-At: 
 <https://www.postgresql.org/message-id/VI1P191MB0638AA48C8FA4AC3CC71B5B9F2362%40VI1P191MB0638.EURP191.PROD.OUTLOOK.COM>
Precedence: bulk

--_005_VI1P191MB0638AA48C8FA4AC3CC71B5B9F2362VI1P191MB0638EURP_
Content-Type: multipart/alternative;
	boundary="_000_VI1P191MB0638AA48C8FA4AC3CC71B5B9F2362VI1P191MB0638EURP_"

--_000_VI1P191MB0638AA48C8FA4AC3CC71B5B9F2362VI1P191MB0638EURP_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

I have got a procedure (test.sql) and this calls a function (test_rr.sql) w=
hich returns a table. I use the SQL:
Call test();

I get the error:
ERROR: invalid input syntax for type integer: "(14891,0,"VIMALBA ","P901 ",=
111.000000)"
CONTEXT: PL/pgSQL function test() line 13 at FETCH
SQL state: 22P02

Can anyone help please

Russell Rose


--_000_VI1P191MB0638AA48C8FA4AC3CC71B5B9F2362VI1P191MB0638EURP_
Content-Type: text/html; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<html xmlns:v=3D"urn:schemas-microsoft-com:vml" xmlns:o=3D"urn:schemas-micr=
osoft-com:office:office" xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:m=3D"http://schemas.microsoft.com/office/2004/12/omml" xmlns=3D"http:=
//www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3Dus-ascii"=
>
<meta name=3D"Generator" content=3D"Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
	{font-family:"Cambria Math";
	panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
	{font-family:Calibri;
	panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
	{font-family:"Source Code Pro";
	panose-1:2 11 5 9 3 4 3 2 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0cm;
	font-size:11.0pt;
	font-family:"Calibri",sans-serif;
	mso-ligatures:standardcontextual;
	mso-fareast-language:EN-US;}
span.EmailStyle17
	{mso-style-type:personal-compose;
	font-family:"Calibri",sans-serif;
	color:windowtext;}
.MsoChpDefault
	{mso-style-type:export-only;
	font-family:"Calibri",sans-serif;
	mso-fareast-language:EN-US;}
@page WordSection1
	{size:612.0pt 792.0pt;
	margin:72.0pt 72.0pt 72.0pt 72.0pt;}
div.WordSection1
	{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext=3D"edit" spidmax=3D"1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext=3D"edit">
<o:idmap v:ext=3D"edit" data=3D"1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang=3D"EN-GB" link=3D"#0563C1" vlink=3D"#954F72" style=3D"word-wrap:=
break-word">
<div class=3D"WordSection1">
<p class=3D"MsoNormal">I have got a procedure (test.sql) and this calls a f=
unction (test_rr.sql) which returns a table. I use the SQL:<o:p></o:p></p>
<p class=3D"MsoNormal">Call test();<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">I get the error:<o:p></o:p></p>
<p class=3D"MsoNormal"><span style=3D"font-size:9.0pt;font-family:&quot;Sou=
rce Code Pro&quot;;color:#222222;letter-spacing:.1pt;background:white">ERRO=
R: invalid input syntax for type integer: &quot;(14891,0,&quot;VIMALBA &quo=
t;,&quot;P901 &quot;,111.000000)&quot;
<o:p></o:p></span></p>
<p class=3D"MsoNormal"><span style=3D"font-size:9.0pt;font-family:&quot;Sou=
rce Code Pro&quot;;color:#222222;letter-spacing:.1pt;background:white">CONT=
EXT: PL/pgSQL function test() line 13 at FETCH
<o:p></o:p></span></p>
<p class=3D"MsoNormal"><span style=3D"font-size:9.0pt;font-family:&quot;Sou=
rce Code Pro&quot;;color:#222222;letter-spacing:.1pt;background:white">SQL =
state: 22P02</span><o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">Can anyone help please<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal"><span style=3D"mso-ligatures:none;mso-fareast-langua=
ge:EN-GB">Russell Rose<o:p></o:p></span></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
</div>
</body>
</html>

--_000_VI1P191MB0638AA48C8FA4AC3CC71B5B9F2362VI1P191MB0638EURP_--

--_005_VI1P191MB0638AA48C8FA4AC3CC71B5B9F2362VI1P191MB0638EURP_
Content-Type: application/octet-stream; name="test_rr.sql"
Content-Description: test_rr.sql
Content-Disposition: attachment; filename="test_rr.sql"; size=517;
	creation-date="Mon, 25 Mar 2024 15:40:35 GMT";
	modification-date="Mon, 25 Mar 2024 15:43:11 GMT"
Content-Transfer-Encoding: base64

RFJPUCBGVU5DVElPTiBJRiBFWElTVFMgcHVibGljLnRlc3RfcnI7CkNSRUFURSBPUiBSRVBMQUNF
IEZVTkNUSU9OIHB1YmxpYy50ZXN0X3JyKCkKICAgIFJFVFVSTlMgVEFCTEUocmV0MSBpbnRlZ2Vy
LCByZXQyIGNoYXIoMSksIHJldDMgY2hhcig4KSwgcmV0NCBjaGFyKDExKSwgcmV0NSBkZWNpbWFs
KDMwLDYpKSAKICAgIExBTkdVQUdFICdwbHBnc3FsJwogICAgQ09TVCAxMDAKICAgIFZPTEFUSUxF
IFBBUkFMTEVMIFVOU0FGRQogICAgUk9XUyAxMDAwCgpBUyAkQk9EWSQKCkJFR0lOCgoJcmV0dXJu
IHF1ZXJ5IFNFTEVDVCAgMTQ4OTE6OmludGVnZXIsCiAgICAgICAgICAgICcwJzo6Y2hhcigxKSwK
ICAgICAgICAgICAgJ1ZJTUFMQkEnOjpjaGFyKDgpLAogICAgICAgICAgICAnUDkwMSc6OmNoYXIo
MTEpLAogICAgICAgICAgICAxMTEuMDA6OmRlY2ltYWwoMzAsNik7CkVORAotLSoqVFNEKioKJEJP
RFkkOwoKQUxURVIgRlVOQ1RJT04gcHVibGljLnRlc3RfcnIoKQogICAgT1dORVIgVE8gcG9zdGdy
ZXM7Cg==

--_005_VI1P191MB0638AA48C8FA4AC3CC71B5B9F2362VI1P191MB0638EURP_
Content-Type: application/octet-stream; name="test.sql"
Content-Description: test.sql
Content-Disposition: attachment; filename="test.sql"; size=509;
	creation-date="Mon, 25 Mar 2024 15:40:35 GMT";
	modification-date="Mon, 25 Mar 2024 15:43:11 GMT"
Content-Transfer-Encoding: base64

LS0gUFJPQ0VEVVJFOiBwdWJsaWMudGVzdCgpCgotLSBEUk9QIFBST0NFRFVSRSBJRiBFWElTVFMg
cHVibGljLnRlc3QoKTsKCkNSRUFURSBPUiBSRVBMQUNFIFBST0NFRFVSRSBwdWJsaWMudGVzdCgK
CSkKTEFOR1VBR0UgJ3BscGdzcWwnCkFTICRCT0RZJAogICAgREVDTEFSRSByZXQxIGludGVnZXI7
CiAgICBERUNMQVJFIHJldDIgY2hhcigxKSA7CiAgICBERUNMQVJFIHJldDMgY2hhcig4KSA7CiAg
ICBERUNMQVJFIHJldDQgY2hhcigxMSkgOwogICAgREVDTEFSRSByZXQ1IGRlY2ltYWwoMzAsNikg
OwoKICAgIERFQ0xBUkUgbXlfY3VyICBzY3JvbGwgQ1VSU09SIEZPUgogICAgICAgIFNFTEVDVCB0
ZXN0X3JyKCkgOwoKQkVHSU4KCQkJT1BFTiBteV9jdXIgOwogICAgICAgICAgICBGRVRDSCBGUk9N
IG15X2N1ciBJTlRPIHJldDEsIHJldDIsIHJldDMsIHJldDQsIHJldDUgOwoKRU5ECiRCT0RZJDsK
QUxURVIgUFJPQ0VEVVJFIHB1YmxpYy50ZXN0KCkKICAgIE9XTkVSIFRPIHBvc3RncmVzOwo=

--_005_VI1P191MB0638AA48C8FA4AC3CC71B5B9F2362VI1P191MB0638EURP_--