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 1ov1Ki-0008J9-08 for pgsql-general@arkaria.postgresql.org; Tue, 15 Nov 2022 19:10:56 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1ov1Kg-0001O0-Ny for pgsql-general@arkaria.postgresql.org; Tue, 15 Nov 2022 19:10:54 +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 1ov1Kg-0001Nr-Do for pgsql-general@lists.postgresql.org; Tue, 15 Nov 2022 19:10:54 +0000 Received: from mail-vk1-xa35.google.com ([2607:f8b0:4864:20::a35]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1ov1Ke-0004MD-Fz for pgsql-general@lists.postgresql.org; Tue, 15 Nov 2022 19:10:54 +0000 Received: by mail-vk1-xa35.google.com with SMTP id e2so7067714vkd.13 for ; Tue, 15 Nov 2022 11:10:52 -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:cc:to :content-language:subject:user-agent:mime-version:date:message-id :from:to:cc:subject:date:message-id:reply-to; bh=TMvtplVzMMOIriY/D9QWo7xviK6sVp4pDv1Fd8OiItM=; b=LCiOW2f7MWfsstd+LmIjmo7JeMQIog3f+67cZxDizXiwPPF+SNUQiNnlXcFDMdvtof a2XaAJIf7j8MpQuYQIZErwQ/ijLxj3QoFsFVLTvNgYmyKGs7e4qTjJPFW8uoohlsd5EO QbhT7Ht8Ij+OVGZGHcNt3r6B5q+Ozm+MwzVb8HGXMlpoe4ljGOU5Wyep8oO0ofmcz2Ay eFqBAsWeOwc1CrgKFKbE+YuT10TtYtGYqT7Jx2/UaJi4bOVaQYossBEnprHw50WQuRMB mWMZjCvlPKRurGLVsCftp+vlRWayaoWquS8xlpVJIpy6YOFyAjUjhZm6R0LZPf7eJpOP ThUw== 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:cc: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=TMvtplVzMMOIriY/D9QWo7xviK6sVp4pDv1Fd8OiItM=; b=tZGTWq7xTr1y8vamnx0ba7+cK2IiUmBbQ2X4S+xWE+sUX0UJaWlP4YTTkMyLkt7xET x3XGT046SX1717UXNi3mHsHpApipmKAfXGt0wX/QWCgZ4SKOJFTOLKqZIQMlu7k+ydT3 zOwT5Xw9ZMd1z+VsOQgGbL97LjAW2AZWQw2G93UgQvAhTxTZlsGksMAvRmo80HAREQTL 7q68yWvNKgbX2TXMR10xiNY2L6K8csb9NgYV4QSj/gE2Rk4nSjHdyqlDVUBPxHvMxj1Y GmcrI7MU9to7jR+bilYn1oXzXpmjFSGwMhAO05e/Fa4NC3KIbXX+5/3zxZ7iORSvi99U zyaw== X-Gm-Message-State: ANoB5pkx0Sd1h1oVEILCDWBTLu3UvpPZORSgxWOf31XfpVV43h5UMb/z 2ypkb/XAQewnKkfojbY8cc4= X-Google-Smtp-Source: AA0mqf40JVqPzEn3AcULt/JG1WF0MInssgzmeFu0BxqCb4ihPeD48h/jvXVXlEM/SFI9SqR7Ws5jFw== X-Received: by 2002:a1f:a184:0:b0:3b6:87b6:b8d4 with SMTP id k126-20020a1fa184000000b003b687b6b8d4mr10509060vke.12.1668539450805; Tue, 15 Nov 2022 11:10:50 -0800 (PST) Received: from ?IPV6:2803:1500:e01:d97d:9c6f:aa78:3036:459b? ([2803:1500:e01:d97d:9c6f:aa78:3036:459b]) by smtp.gmail.com with ESMTPSA id j12-20020a056122090c00b003bbfc4cb34esm1159925vka.25.2022.11.15.11.10.48 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Tue, 15 Nov 2022 11:10:49 -0800 (PST) Message-ID: Date: Tue, 15 Nov 2022 15:10:47 -0400 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:102.0) Gecko/20100101 Thunderbird/102.4.2 Subject: Re: Calling function from VFP changes character field to Memo Content-Language: en-GB To: Adrian Klaver , Tom Lane Cc: pgsql-general@lists.postgresql.org References: <1177304.1668537843@sss.pgh.pa.us> <7dc224a2-df53-de4a-42a6-a61502c71d95@gmail.com> From: Frank Cazabon In-Reply-To: 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 15/11/2022 2:58 pm, Adrian Klaver wrote: > On 11/15/22 10:54 AM, 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' > > No you don't want to do that: > > select 'test'::char; >  bpchar > -------- >  t > > vs > > select 'test'::varchar; >  varchar > --------- >  test > > Besides you missed the important part, after creating the type > testfunction_result: > > create function testfunction() returns setof testfunction_result as ... > Sorry about the confusion, I have got it working using the type definition. Thanks for the help > >> >> I'll try the type definition and see if that helps. >> >> > >