agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: skinner@britvault.co.uk
To: pgsql-sql@postgresql.org
Subject: Re: Function args: TEXT -vs- VARCHAR?
Date: Wed, 13 Nov 2013 14:17:18 +0000
Message-ID: <20131113141718.GA29223@teak.britvault.co.uk> (raw)
In-Reply-To: <20131113130308.GA17439@teak.britvault.co.uk>
References: <20131112181744.GA7522@teak.britvault.co.uk>
	<CAHZ=uVAOB9H9jS2BGnu9jDKosxapKY2_M+arf+-kC1fwCSgvfg@mail.gmail.com>
	<20131113130308.GA17439@teak.britvault.co.uk>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>

On 2013-11-13 Wed 13:03 PM |, Craig R. Skinner wrote:
> 
> Yes, I'll change the function args to be the same as the table columns
> so the functions fail on over length input, rather than going through
> the process of validating customer id & account, only to fail on data.
> 
> Therefore => performace increase with character varying function args.
> 

Well, I got a SURPRISE there as it seems PostgreSQL function arguments
loose their precision.

Character varying(n) args become character varying / text.

I would have thought that this function should fail when called, not at
the INSERT phase:


--=======


CREATE TABLE rubbish
(
	junk	character varying(12) NOT NULL
);


--=======


CREATE OR REPLACE FUNCTION load_rubbish(gash character varying(12))
RETURNS boolean AS
$BODY$

BEGIN
	INSERT INTO rubbish
	(
		junk
	)
	VALUES
	(
		gash
	);

	RETURN FOUND;
END;

$BODY$ LANGUAGE plpgsql;


--=======


SELECT * FROM load_rubbish('Waaaay toooo loooong!');


--=======


********** Error **********

ERROR: value too long for type character varying(12)
SQL state: 22001
Context: SQL statement "INSERT INTO rubbish
	(
		junk
	)
	VALUES
	(
		gash
	)"
PL/pgSQL function load_rubbish(character varying) line 4 at SQL statement
                                               ^^^
                                    The length limit has gone.





This page does not say the precision is stripped:

"... Functions written in PL/pgSQL can accept as arguments any scalar or
array data type supported by the server, ...."
http://www.postgresql.org/docs/9.2/static/plpgsql-overview.html#PLPGSQL-ARGS-RESULTS


Neither does this page:
argtype
	".... The argument types can be base, ...."
http://www.postgresql.org/docs/current/static/sql-createfunction.html


Comments?
-- 
Craig Skinner | http://twitter.com/Craig_Skinner | http://linkd.in/yGqkv7


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

view thread (17+ messages)  latest in thread

Message-ID: <20131113141718.GA29223@teak.britvault.co.uk>
Permalink:  ../20131113141718.GA29223@teak.britvault.co.uk/
Also on:    postgresql.org/message-id/20131113141718.GA29223@teak.britvault.co.uk

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: skinner@britvault.co.uk
  Subject: Re: Function args: TEXT -vs- VARCHAR?
  In-Reply-To: <20131113141718.GA29223@teak.britvault.co.uk>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox