Re: [SQL] Our FLOAT(p) precision does not conform to spec
Fix the problem and inform the users about code that may break. Rick ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] [Newbie] migrating a stored procedure from MSSQL to postgresql
As declared, your function returns TEXT, i.e. unlimited characters. >> CREATE FUNCTION UpdateOrder(INTEGER) RETURNS TEXT AS Since your variable >> r_SKUPrice RECORD; contains a number of columns >> SELECT SKU, Price INTO r_SKUPrice you could create a composite TYPE that matches those columns and since your variable can contain a number of such rows, (see the select above) the function needs to become a set returning function CREATE FUNCTION UpdateOrder(INTEGER) RETURNS SETOF AS Rick Bengali wrote: Hi, I am a postgresql and stored procedures beginner and I would like to know if the stored procedure I am trying to migrate to plpgsql from MSSQL is correct. Here 's the only table involved in the stored procedure: create table ManufacturerOrders ( OrderNumber serial, SKU int not null, Make varchar(50) not null, Model varchar(50) not null, Price int not null, Status varchar(20) not null, primary key (OrderNumber) ); Here 's the original MSSQL stored procedure: create procedure UpdateOrder (@OrderNum int) as set nocount on update ManufacturerOrders set Status = "Shipped" where OrderNumber = @OrderNum; SELECT SKU, Price FROM ManufacturerOrders WHERE OrderNumber = @OrderNum go Here 's the plpgsql version i wrote: CREATE FUNCTION UpdateOrder(INTEGER) RETURNS TEXT AS ' DECLARE i_ordernum ALIAS for $1; r_SKUPrice RECORD; BEGIN update ManufacturerOrders set Status = ''Shipped'' where OrderNumber = i_ordernum; SELECT SKU, Price INTO r_SKUPrice FROM ManufacturerOrders WHERE OrderNumber = i_ordernum; return r_SKUPrice; END; ' LANGUAGE 'plpgsql'; I would like to know especially if the RETURNS statement is correct here and if i can give a name to the record r_SKUPrice columns . Thanks in advance, Bengali ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] plpgsql doesn't coerce boolean expressions to boolean
Define the language! If it breaks code, so be it. 2. Throw an error if the _expression_ doesn't return boolean. Yes, yes, absolutely. By definition "an IF, WHILE, or EXIT statement is a boolean _expression_" SO if "some stupid piece of text" THEN should not compile, there is no BOOLEAN _expression_. C's implementation of hat is true and false has always, IMHO, been hideous. But then again, I am a Pascal kind of thinker. An integer with a value of 1 is still only an integer, IF I <> 0 THEN ... is clear and un-ambiguous. Tom Lane wrote: Following up this gripe http://archives.postgresql.org/pgsql-sql/2003-09/msg00044.php I've realized that plpgsql just assumes that the test _expression_ of an IF, WHILE, or EXIT statement is a boolean _expression_. It doesn't take any measures to ensure this is the case or convert the value if it's not the case. This seems pretty bogus to me. However ... with the code as it stands, for pass-by-reference datatypes any nonnull value will appear TRUE, while for pass-by-value datatypes any nonzero value will appear TRUE. I fear that people may actually be depending on these behaviors, particularly the latter one which is pretty reasonable if you're accustomed to C. So while I'd like to throw an error if the argument isn't boolean, I'm afraid of breaking people's function definitions. Here are some possible responses, roughly in order of difficulty to implement: 1. Leave well enough alone (and perhaps document the behavior). 2. Throw an error if the _expression_ doesn't return boolean. 3. Try to convert nonbooleans to boolean using plpgsql's usual method for cross-type coercion, ie run the type's output proc to get a string and feed it to bool's input proc. (This seems unlikely to avoid throwing an error in very many cases, but it'd be the most consistent with other parts of plpgsql.) 4. Use the parser's coerce_to_boolean procedure, so that nonbooleans will be accepted in exactly the same cases where they'd be accepted in a boolean-requiring SQL construct (such as CASE). (By default, none are, so this isn't really different from #2. But people could create casts to boolean to override this behavior in a controlled fashion.) Any opinions about what to do? regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] how to call a function with row-type arg
DECLARE I INTEGER; BEGIN SELECT * INTO I FROM foo() That part is easy, but I don't understand what you are using as a function parameter. Rick sad wrote: hi how to call a function with a row_type arg ?? that is the question CREATE FUNCTION foo(tablename) returns int . SELECT foo ( ??? ); ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
