Обсуждение: create constant values
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.
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.
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
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'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
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