User-Defined Variables

Hello

PostgreSQL doesn't support this feature. There are some techniques
that you can use:
http://www.pgsql.cz/index.php/PostgreSQL_SQL_Tricks#Any_other_session_variables
http://www.postgresql.org/docs/8.3/static/plperl-global.html

Regards
Pavel Stehule

2008/7/10 Daniel Futerman <daniel.futerman@gmail.com>:

On Thursday 10. July 2008, Daniel Futerman wrote:

Hi,

Is there a quick solution to implementing user-defined variables in
PostgreSQL as they are used in MySQL?

I have the following MySQL script which i want to implement in
Postgres (NOTE : all ` have been changed to " for Postgres use):

SET @OTHER_CONCEPT_ID = (SELECT "concept_id" FROM "concept_name" where
name = 'MRO' LIMIT 1);

You should probably think about using a function instead, like eg:

CREATE OR REPLACE
FUNCTION other_concept_id(INTEGER) RETURNS INTEGER AS $$
SELECT concept_id FROM concept_name where name = 'MRO' LIMIT 1;
$$ LANGUAGE sql STABLE;

hth,
--
Leif Biberg Kristensen | Registered Linux User #338009
Me And My Database: http://solumslekt.org/blog/
My Jazz Jukebox: http://www.last.fm/user/leifbk/

Don't forget to cc: the list

Daniel Futerman wrote:

What is the script trying to do (in a wider sense)?

The variable is used as follows:

SET @OTHER_CONCEPT_ID = (SELECT `concept_id` FROM `concept_name` where name
= 'MRO' LIMIT 1);

(SELECT
COALESCE(f2.concept_id, @OTHER_CONCEPT_ID ) as 'concept_id'
FROM
`field` f, `field` f2, `form_field` ff, `form_field` ff2, `form`
WHERE
form.form_id = ff.form_id AND
ff.field_id = f.field_id AND
f.concept_id = obs.concept_id AND
ff.parent_form_field = ff2.form_field_id AND
ff2.field_id = f2.field_id
LIMIT 1
);

I don't see what this gets you that a subquery / join doesn't.

SELECT
COALESCE(f2.concept_id, default_concept.concept_id) AS concept_id
FROM
...
,(SELECT concept_id FROM concept_name WHERE name='MRO') AS
default_concept
WHERE

I've left the LIMIT 1 off since I'm assuming name is unique - if not the
LIMIT 1 doesn't make any sense without an ORDER BY too.

--
Richard Huxton
Archonet Ltd