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 1ov1A1-0007dU-Qz for pgsql-general@arkaria.postgresql.org; Tue, 15 Nov 2022 18:59:53 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1ov1A0-0008C9-ME for pgsql-general@arkaria.postgresql.org; Tue, 15 Nov 2022 18:59:52 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ov1A0-0008C0-9u for pgsql-general@lists.postgresql.org; Tue, 15 Nov 2022 18:59:52 +0000 Received: from mail-oi1-x236.google.com ([2607:f8b0:4864:20::236]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1ov19w-0002CI-F8 for pgsql-general@lists.postgresql.org; Tue, 15 Nov 2022 18:59:51 +0000 Received: by mail-oi1-x236.google.com with SMTP id v81so15852204oie.5 for ; Tue, 15 Nov 2022 10:59:48 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=content-transfer-encoding:in-reply-to:from:references:to :content-language:subject:user-agent:mime-version:date:message-id :from:to:cc:subject:date:message-id:reply-to; bh=0R0ig2Hqac7ZxBxySxMxvIIWKRMoK6zZQzmpnDEAKrI=; b=Ozx1VnU7cdSeqm3Ws2OAKSgSYaIgnZN60YslrTpMcgV9xPRZhjmF6HauGRrzqGKD4z GSM11GN8NknuEKMYwRL1VonHbypva4ed2v60HypSFsYwthHZ0KqWMPWDQXMQ4H8Bo6rv JWfrNMiKw39Jwxdy4jzYW5tP2lsrD56MGqkeum+QQTWJs3hbvHFpfqMDs3zRzvtwRUsD Pfg0BlEQe7JU8NQBoViYhX0NqAyTBOBsaCkT46nuF0i5Lexbrz0jHN7/MCDFw3FqQqHB iBd6hBI+iCkQWGE+gLemtex7dku+8Lrz6zDhOtEHrq7smuMKPOcM+RIRDKYm7q2ImfGe yG5w== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=content-transfer-encoding:in-reply-to:from:references:to :content-language:subject:user-agent:mime-version:date:message-id :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=0R0ig2Hqac7ZxBxySxMxvIIWKRMoK6zZQzmpnDEAKrI=; b=6PJVwhIpzTZxJbbTsBAWoA62J26poVllDy1FQ1l/JcukFqsyHi+tI4G2SEuhEcTysK 5vXYXnc8xFOn/IRCgR/1tFaxQxU2YQLCC/XwLgT5eDlmpGeRBAc0l07J9qbPw2cdjlsd zycb2ewqmiccU2Kk78OLwyBsWSl7ztm3Oao181PCnnT0txYZyq9h8cwlu31z1fu4qx9E btYZk6NuMgcoqGLWHn5+8zuB5waVt+pKaUjb5MTUtu1Kk+ESWtCmHeTeFxywYujRtoLg C4M5YhjLh9kWKQelyxGi2Bi/M8BrE0xbrFS8eGtp0jV9l12cUGmyKRv8y9RLfXvkC2Ug nX8g== X-Gm-Message-State: ANoB5pmcPM11P9fpF/4v5bfoHvQCCNyjY+fL8d9f4Qbrno9ktmlBtKCN gRGAPuTYegPAeNaC+7954M/fnh3tQmw= X-Google-Smtp-Source: AA0mqf44BJYoIkDKigM51OtyK7SNN4mqQFac6+ZTDNr55VDl+GL0BTVpPjFVPxi1pXzyCDuhDY1LTA== X-Received: by 2002:a54:458c:0:b0:35a:fc51:30e5 with SMTP id z12-20020a54458c000000b0035afc5130e5mr1078967oib.31.1668538787469; Tue, 15 Nov 2022 10:59:47 -0800 (PST) Received: from ?IPV6:2600:1700:bd2c:b810::43? ([2600:1700:bd2c:b810::43]) by smtp.googlemail.com with ESMTPSA id h3-20020a9d7983000000b0063b24357269sm5635989otm.13.2022.11.15.10.59.42 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Tue, 15 Nov 2022 10:59:44 -0800 (PST) Message-ID: <81af1ec5-de31-03c6-96c4-fd70f12297a8@gmail.com> Date: Tue, 15 Nov 2022 12:59:40 -0600 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.2.2 Subject: Re: Calling function from VFP changes character field to Memo Content-Language: en-US To: pgsql-general@lists.postgresql.org References: <1177304.1668537843@sss.pgh.pa.us> <7dc224a2-df53-de4a-42a6-a61502c71d95@gmail.com> From: Ron In-Reply-To: <7dc224a2-df53-de4a-42a6-a61502c71d95@gmail.com> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 11/15/22 12:54, Frank Cazabon wrote: > > 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( >>>       ) >>>       RETURNS TABLE >>>       ( >>>           Firstname character(30) >>>       ) >>>       LANGUAGE 'plpgsql' >>> AS $BODY$ >>> BEGIN >>>       RETURN QUERY SELECT p.cFirstName FROM patients 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. >> >>> 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. >> >> You could perhaps work around that by defining a named composite >> type: >> >> create type testfunction_result as (firstname character(30), ...); >> >> create function testfunction() returns setof testfunction_result as ... >> >>             regards, tom lane > Thanks, so I could define the function like this - removed the (30): > > CREATE OR REPLACE FUNCTION public.testfunction( >      ) >      RETURNS TABLE >      ( >          Firstname character >      ) >      LANGUAGE 'plpgsql' > > I'll try the type definition and see if that helps. I think you should try the original function in psql.  That will delineate Postgresql from framework. -- Angular momentum makes the world go 'round.