Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ov1HL-00085u-MT for pgsql-general@arkaria.postgresql.org; Tue, 15 Nov 2022 19:07:27 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1ov1HK-0002rV-H9 for pgsql-general@arkaria.postgresql.org; Tue, 15 Nov 2022 19:07:26 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ov1HK-0002pb-3k for pgsql-general@lists.postgresql.org; Tue, 15 Nov 2022 19:07:26 +0000 Received: from mail-vs1-xe2d.google.com ([2607:f8b0:4864:20::e2d]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1ov1HH-0004K3-Qm for pgsql-general@lists.postgresql.org; Tue, 15 Nov 2022 19:07:25 +0000 Received: by mail-vs1-xe2d.google.com with SMTP id z189so15652904vsb.4 for ; Tue, 15 Nov 2022 11:07:23 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=content-transfer-encoding:mime-version:subject:references :in-reply-to:message-id:cc:to:from:date:from:to:cc:subject:date :message-id:reply-to; bh=WlAM6PkGvC+IRE5RLIaYm/IG3DESJfWuvPPGnP9rIso=; b=nhqHo3jenGSPEoTnlmcOV1+Jb6mzXOWbIeCIv7urJdbQY6/E+hN2dautCx+0Kg8gkC Opy43GPLc7KyXug0gc+jH8zHZQbO8j4J2sq4E8nT52h5yy37HvUEVh4JonZiuD31QTBt qi7R8qIj+7LgqcwiQXdepvk/2VUpiApIzoNnRqLl/vc40/v48i2LIsv0ZkTGOsFtzU09 vklUiur6wNvFNQZ9f33xfhwOsJAk3aH335eYS3+RO3KzRjfiMZ2Mahu2obadKmOAS49U 5plqT+avn4LKcpoP69wVGnuHCJYvtNSmhd4HowiSWmT2gWvEZo5lfRIOwz4T0kpdRvMz nyuA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=content-transfer-encoding:mime-version:subject:references :in-reply-to:message-id:cc:to:from:date:x-gm-message-state:from:to :cc:subject:date:message-id:reply-to; bh=WlAM6PkGvC+IRE5RLIaYm/IG3DESJfWuvPPGnP9rIso=; b=SkDN0QQj+PqNcO+irKqFFDbuhPxV6JIn4wzJEVGeoq1HCcYndwOJwFOJC4RSau/pU6 yzGjQSzQqyFpNUJOfb3reZsN9KNoDEU/O5apq71bRwtCn9OHKouN8ikXwE/KeEiOhDFf RDKJ77r1d/bjTWjdrEgohCrlS6qRDrXglrvsn/sCyjOLL332tYwtDgFWrAcqEdLS1jp5 +Fq0maud1BRuCA3fI87ihrzLNjH70PqLTOEjiMOCJANZwTCaSTzWQ5ibf2qXOI4oh0ba Mw7bqUoNYGzzbSiyRQwJt08gyzL0zdbQP8Pdnwwc5lm1hHeevGUJMdOcCggk0QoB6wHN 1Hsw== X-Gm-Message-State: ANoB5pnD2ddeKv92IBekUfe/rONydeemebTOnweBxTwbJwEeCBxV6Iyn CBIMniF/nfm1anrXeYdcmHM= X-Google-Smtp-Source: AA0mqf6HB+yllTA3NJEXN6vSWGDEKQ4wrJMjWQP5t5+ks2wGlGRV0x/a+m3ZV2kweZ5LBtG0mZd24A== X-Received: by 2002:a67:f454:0:b0:3aa:12a1:c023 with SMTP id r20-20020a67f454000000b003aa12a1c023mr9610512vsn.59.1668539242002; Tue, 15 Nov 2022 11:07:22 -0800 (PST) Received: from [127.0.0.1] ([161.0.158.12]) by smtp.gmail.com with ESMTPSA id d77-20020a1fb450000000b003b807749ddasm1809006vkf.30.2022.11.15.11.07.17 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Tue, 15 Nov 2022 11:07:18 -0800 (PST) Date: Tue, 15 Nov 2022 15:07:16 -0400 (AST) From: Frank Cazabon To: Ron Cc: pgsql-general@lists.postgresql.org Message-ID: In-Reply-To: <81af1ec5-de31-03c6-96c4-fd70f12297a8@gmail.com> References: <1177304.1668537843@sss.pgh.pa.us> <7dc224a2-df53-de4a-42a6-a61502c71d95@gmail.com> <81af1ec5-de31-03c6-96c4-fd70f12297a8@gmail.com> Subject: Re: Calling function from VFP changes character field to Memo MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable X-Correlation-ID: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk I don't think that's necessary, I'm 100% certain that it's VFP not able to = interpret the size of what is coming back to it so it just gives it the big= gest type it can. Thanks 15 Nov 2022 14:59:59 Ron : > On 11/15/22 12:54, Frank Cazabon wrote: >>=20 >> On 15/11/2022 2:44 pm, Tom Lane wrote: >>> Frank Cazabon writes: >>>> If however I have a function defined like this >>>> CREATE OR REPLACE FUNCTION public.testfunction( >>>> =C2=A0 =C2=A0=C2=A0 =C2=A0) >>>> =C2=A0 =C2=A0=C2=A0=C2=A0 RETURNS TABLE >>>> =C2=A0 =C2=A0=C2=A0 =C2=A0( >>>> =C2=A0 =C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0=C2=A0 Firstname character(30) >>>> =C2=A0 =C2=A0=C2=A0 =C2=A0) >>>> =C2=A0 =C2=A0=C2=A0=C2=A0 LANGUAGE 'plpgsql' >>>> AS $BODY$ >>>> BEGIN >>>> =C2=A0 =C2=A0=C2=A0=C2=A0 RETURN QUERY SELECT p.cFirstName FROM patien= ts p; >>>> END; >>>> $BODY$; >>>> And I call: >>>> SELECT * FROM public.testFunction(); >>>> Then FirstName returns as a Memo field (similar to a Text field). >>> This is mostly about whatever software stack you're using on the >>> client side --- Memo is certainly not something Postgres knows about. >>>=20 >>>> Any idea what I need to do to get it to return the character(30) type? >>> There's no chance of getting back the "30" part with this structure, >>> because function signatures do not carry length restrictions. >>> What I expect is happening is that you get firstname as an >>> unspecified-length "character" type, and something on the client >>> side is deciding to cope with that by calling it "Memo" instead. >>>=20 >>> You could perhaps work around that by defining a named composite >>> type: >>>=20 >>> create type testfunction_result as (firstname character(30), ...); >>>=20 >>> create function testfunction() returns setof testfunction_result as ... >>>=20 >>> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 rega= rds, tom lane >> Thanks, so I could define the function like this - removed the (30): >>=20 >> CREATE OR REPLACE FUNCTION public.testfunction( >> =C2=A0=C2=A0=C2=A0 =C2=A0) >> =C2=A0=C2=A0=C2=A0=C2=A0 RETURNS TABLE >> =C2=A0=C2=A0=C2=A0 =C2=A0( >> =C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0=C2=A0 Firstname character >> =C2=A0=C2=A0=C2=A0 =C2=A0) >> =C2=A0=C2=A0=C2=A0=C2=A0 LANGUAGE 'plpgsql' >>=20 >> I'll try the type definition and see if that helps. >=20 > I think you should try the original function in psql.=C2=A0 That will del= ineate Postgresql from framework. >=20 > --=20 > Angular momentum makes the world go 'round.