Postgres Pro
Downloads
Home   >   mailing lists

Thread: create constant values

create constant values

From
john@msasystems.net
Date:
Hi,
 
Is there anyway to create constant values that could be used and reused in postgres.
Sort of a CREATE CONSTANT name VALUE foo AS integer
 
or some such.
 
Thanks.
 
 

Re: create constant values

From
"Rodrigo De León"
Date:
On Nov 21, 2007 12:02 PM,  <john@msasystems.net> wrote:
> Is there anyway to create constant values that could be used and reused in
> postgres.

What are you trying to do?

If you share the specific requirements you have, maybe someone on this
list can help you.

Re: create constant values

From
john@msasystems.net
Date:
Hi,
 
   i have a:

                     select
*from foo where type = 1 and score > 12; 
 
   i would like to say
 

                    
select* from foo where type = QUARTERBACK and score > 12; 
 
 
Just an example.
 
John
 
-------- Original Message --------Subject: Re: [NOVICE] create constant valuesFrom: "Rodrigo_De_León"
<rdeleonp@gmail.com>Date:Wed, November 21, 2007 10:32 amTo: john@msasystems.netCc: pgsql-novice
<pgsql-novice@postgresql.org>OnNov 21, 2007 12:02 PM, <john@msasystems.net> wrote:> Is there anyway to
createconstant values that could be used and reused in> postgres.What are you trying to do?If you share the specific
requirementsyou have, maybe someone on thislist can help you.---------------------------(end of
broadcast)---------------------------TIP1: if p 
 osting/reading through Usenet, please send an appropriatesubscribe-nomail command to majordomo@postgresql.org so that
yourmessagecan get through to the mailing list cleanly

Execute RAISE problems

From
"Robert M. Bernabe"
Date:
Hi,

I've been trying to create a debugging function that would receive a tablename and a list of columns and then the function would display all the contents of the table using the RAISE command.

CREATE OR REPLACE FUNCTION usp_PG_DUMPTEMPTABLE(varchar(100), text[])
RETURNS void AS
$BODY$
DECLARE
_temptable ALIAS FOR $1;
_temparray alias for $2;
_i integer;
_max integer;
_tempstring varchar(2000);

BEGIN
_tempstring := 'RAISE INFO''';
for _i in 1 ..array_upper(_temparray, 1)
loop
_tempstring := _tempstring || _temparray[_i] || ' ' ;
end loop;
_tempstring := _tempstring || ''';';

raise info'%', _tempstring;
execute _tempstring;
raise info '---';

RETURN;
END;
$BODY$
LANGUAGE 'plpgsql';


for some reason, the EXECUTE command issues an error at the start fo the RAISE command...help?

Error from PG Admin III

ERROR: syntax error at or near "RAISE"
LINE 1: RAISE DEBUG 'SKUID CatID ';
^
QUERY: RAISE DEBUG 'SKUID CatID ';
CONTEXT: PL/pgSQL function "usp_pg_dumptemptable" line 42 at EXECUTE statement

Checked all the docs I could find and I couldn't find any info...
 
I couldn't figure out why....so a simple test would be just to execute a literal string (instead of a variable holding a dynamic one) with a raise command...turns out execute cannot run the raise command...
 
e.g.
 execute 'RAISE INFO ''test ''';
 
issues an Error in PG Admin III.
 
any help would be greatly appreciated....I think RAISE was never intended to be used this way...but as you can see in the function, it could prove useful once finished...the ability to display contents of a table for debugging... 
 
Regards
 
 
 

I'm protected by SpamBrave

Re: create constant values

From
Michael Glaesemann
Date:
On Nov 21, 2007, at 13:16 , john@msasystems.net wrote:

> Hi,
>
>    i have a:
>                      select * from foo where type = 1 and score > 12;
>
>    i would like to say
>
>                      select * from foo where type = QUARTERBACK and
> score > 12;

You should have a table which enumerates your types, so you can do
something like:

SELECT *
   FROM foo
   JOIN foo_types USING (type)
   WHERE foo_types.type = 'quarterback'
       AND score > 12;

Magic numbers in a database (or anywhere else for that matter) is not
a good idea.

(Or use the enum types in 8.3, currently beta3)

Michael Glaesemann
grzm seespotcode net