Thread: User-Defined Variables

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);

Thanks,
Daniel.

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>:
> 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);
>
> Thanks,
> Daniel.
>

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