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 1ov14h-0007IR-7g for pgsql-general@arkaria.postgresql.org; Tue, 15 Nov 2022 18:54:23 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1ov14f-00041L-9Q for pgsql-general@arkaria.postgresql.org; Tue, 15 Nov 2022 18:54:21 +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 1ov14e-00041B-VN for pgsql-general@lists.postgresql.org; Tue, 15 Nov 2022 18:54:20 +0000 Received: from mail-vs1-xe2d.google.com ([2607:f8b0:4864:20::e2d]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1ov14c-00025x-Rp for pgsql-general@lists.postgresql.org; Tue, 15 Nov 2022 18:54:19 +0000 Received: by mail-vs1-xe2d.google.com with SMTP id t5so15620902vsh.8 for ; Tue, 15 Nov 2022 10:54:18 -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=PCEYGEWQX1iL5p7VtTzvMw6B11N8Q4nSoWbx8ExXogA=; b=nbJ+sYSXvuEsBXU9Im/8A7UZ0qBxdsXy9D/Bbl9hBIxTnHhZ8lKRHuH36qxY4Rc8cC qEWiAuS+gLiYB5ccQ2ySseYlME26P7iLWJDoWO+1dypiPGXp0sOpSnsF5YeF8aZ8Ayyq aNrYeRx8TLipCl//nr4c5SbBillSreOsCxPr/CyPpFYv9+vzl/f90AF/ML9Q+g73ka8E JKkJnMY3dnqvHoNHDBb83wcMHbhq5FbgeulNdfOCMUzVXECOpDilGB/a+rqEH8BAKaXl 4ur9qlUcKfeWz5dw+EauXD3qdNtT7Tv8rTKJWSBMOmQ5mCGAGvHoj45lPh0RSai0sgFU XYIQ== 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=PCEYGEWQX1iL5p7VtTzvMw6B11N8Q4nSoWbx8ExXogA=; b=mUj90ql9qeGMj8OmgiCUtT8CQVClNy7UNbfUYi+ZVgv8tbQR29sc5Cxaj6mEB1fn0G WShOZZuMunTlx8Fu7zBbQe3NdLatLIAEMm1Q38OyFTi9jJyxsqWfG4qqH7FM3A/Djdnw Yt4CRHn9J4Uj8LZ/0fQOZTdsaVq5btcwp5zhrBjTuohqIe/oB6V7xqIbCRQJpjAHp/tq ESij2nnwB/fxtQ9Hex9pXAIai01M/H6dkUvv7dI6Mki+/76s+i3mzfSERuSPf0fvBg3S lC3Hyg7B/PMevM0j75gTJ372ZP8c+qR2hWBA9NMLGAzgJnyRMnKr4x1Y14k4+0mrfjiX 1ocg== X-Gm-Message-State: ANoB5pnZPTXRDuJW9PzMXyjZqNqQCgPd9CjxLZXeUrhKOB6aDMSEyGSl OrzO4nTmp/lFsjVjk2AnnDY= X-Google-Smtp-Source: AA0mqf4JOve6LJhtVw++RUVh3tcXqTJGUi83ETMtsKMsa8iBFz2lIcBCs9DMdmOvHoGC4voTLpo6gQ== X-Received: by 2002:a05:6102:ccc:b0:3ac:9fa4:ea67 with SMTP id g12-20020a0561020ccc00b003ac9fa4ea67mr9684699vst.47.1668538458119; Tue, 15 Nov 2022 10:54:18 -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 a1-20020a67f2c1000000b003a7d2bec130sm1508933vsn.28.2022.11.15.10.54.16 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Tue, 15 Nov 2022 10:54:17 -0800 (PST) Message-ID: <7dc224a2-df53-de4a-42a6-a61502c71d95@gmail.com> Date: Tue, 15 Nov 2022 14:54:15 -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: Tom Lane Cc: pgsql-general@lists.postgresql.org References: <1177304.1668537843@sss.pgh.pa.us> From: Frank Cazabon In-Reply-To: <1177304.1668537843@sss.pgh.pa.us> 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: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.