Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2015
    Posts
    10

    Unanswered: Basic use of variables in Postgresql?

    Hello everyone,

    I am a grad student with not a lot of database experience, but I am forced to use Postgres for my current project. My problem is that I can't seem to figure out how to use variables in my queries. For example, I have a query where I want to filter data coming in from a device on several different channels. It would look like this:

    Code:
    SELECT *
    FROM controller_events.log
    WHERE channel = 6
    Instead of hard-coding the "6" I want to do something like this:

    Code:
    DECLARE @channel int
    SET @channel = 6
    
    SELECT *
    FROM controller_events.log
    WHERE channel = @channel
    How do I do this in Postgres? I have been searching online, and am completely overwhelmed by the information. I see things about using "SELECT INTO" statements, defining custom functions, temp tables, and PSQL (whatever this is).

    For the queries that I'm working on, I would like to have them work in an ODBC connection with Excel, so I think temp table options are out (Excel's Postgres ODBC driver can't handle temp tables).

    In my mind, this question seems pretty basic, and I am frustrated that I can't seem to find a basic answer. Can anybody help me out with this?
    Last edited by slavrenz; 08-11-15 at 19:05.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    What exactly is your question?

    Variable declaration and usage in PL/pgSQL is fully documented in the manual: http://www.postgresql.org/docs/curre...c/plpgsql.html
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  3. #3
    Join Date
    Mar 2015
    Posts
    10
    Duplicate post deleted
    Last edited by slavrenz; 08-13-15 at 02:57. Reason: Duplicate

  4. #4
    Join Date
    Mar 2015
    Posts
    10

    Using Variables in Postgres: Attempt #2

    Duplicate post deleted
    Last edited by slavrenz; 08-13-15 at 02:57. Reason: Duplicate

  5. #5
    Join Date
    Mar 2015
    Posts
    10
    See the original post. Somehow this website messed it up for over a day, and refused to show the text. Sorry for the confusion.

  6. #6
    Join Date
    Mar 2015
    Posts
    10
    Quote Originally Posted by shammat View Post
    What exactly is your question?

    Variable declaration and usage in PL/pgSQL is fully documented in the manual: http://www.postgresql.org/docs/curre...c/plpgsql.html
    Do you have any more targeted suggestions than this? My problem is not that there's not information out there. My problem is that I am overwhelmed with the options, and I'm not really sure which solution I need.

  7. #7
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    DECLARE @channel int
    SET @channel = 6

    SELECT *
    FROM controller_events.log
    WHERE channel = @channel
    SQL does not have variables and Postgres does not have any extension to the SQL standard that would support "ad-hoc" variables like that (like nearly every other relational DBMS out there - except for SQL Server if I'm not mistaken)

    In Postgres (and many other databases like Oracle, DB2 or Firebird) there is a clear distinction between plain SQL (DML, DDL statements) and procedural code (e.g. PL/pgSQL, PL/SQL, SQL/PL and so on). Variables are only allowed in procedural code, not plain SQL.

    To do something like the in Postgres, you would need support from the SQL client you are using.

    Quote Originally Posted by slavrenz
    For the queries that I'm working on, I would like to have them work in an ODBC connection with Excel, so I think temp table options are out (Excel's Postgres ODBC driver can't handle temp tables).
    I don't work with Excel so I can't comment on the features of Excel as a SQL client, but couldn't you just define a parameter in Excel that is then passed to the SQL query? ODBC definitely supports parameterized queries. Using a ? as a placeholder for which the application executing that statement then provides a value at runtime.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  8. #8
    Join Date
    Mar 2015
    Posts
    10
    Quote Originally Posted by shammat View Post
    SQL does not have variables and Postgres does not have any extension to the SQL standard that would support "ad-hoc" variables like that (like nearly every other relational DBMS out there - except for SQL Server if I'm not mistaken)

    In Postgres (and many other databases like Oracle, DB2 or Firebird) there is a clear distinction between plain SQL (DML, DDL statements) and procedural code (e.g. PL/pgSQL, PL/SQL, SQL/PL and so on). Variables are only allowed in procedural code, not plain SQL.

    To do something like the in Postgres, you would need support from the SQL client you are using.

    I don't work with Excel so I can't comment on the features of Excel as a SQL client, but couldn't you just define a parameter in Excel that is then passed to the SQL query? ODBC definitely supports parameterized queries. Using a ? as a placeholder for which the application executing that statement then provides a value at runtime.
    There's a way to do this in Excel with string concatenation of the queries, but it's a PITA, and I was hoping for a native solution.

    So, there are no methods of query construction that will even "simulate" the use of variables in Postgres? What about any of the functions, SELECT INTO(), etc. to which I previously alluded?

    Finally...this is just venting on my part...but I fail to understand why native capability to handle variables would not be a major consideration in the development of DBMS software. Am I really in some small minority of users who would see value in this? I am used to working with SQL Server/Transact-SQL, and from what I have seen from Postgres so far, I am definitely not impressed.

  9. #9
    Join Date
    Mar 2015
    Posts
    10
    The closest I have come so far to a solution is this:

    Code:
    CREATE TEMP TABLE Parameters
    (
                    id text,
                    value text
    );
    INSERT INTO Parameters(id, value) VALUES
                    ('signal_id', '4437'),
                    ('start_tstamp', '2015-02-01 00:00:00.0'),
                    ('end_tstamp', '2015-02-28 23:59:59.9'),
                    ('phase', '8'),
                    ('channel', '5');
    
    CREATE FUNCTION param(id text)
                    RETURNS text STABLE LANGUAGE SQL AS
                    'SELECT value FROM Parameters WHERE id = $1';
    
    SELECT param('signal_id')::SMALLINT; --returns 4437 as type “smallint”
    It uses a temp table, but it's better than nothing. Unfortunately, it also doubles the length of time that it takes my queries to run (I have no idea why), so I can't use it regularly.

  10. #10
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by slavrenz View Post
    Am I really in some small minority of users who would see value in this? I am used to working with SQL Server/Transact-SQL
    Well if you were coming from an Oracle, DB2, Firebird or Teradata background (or any other DBMS that isn't MySQL or SQL Server) you wouldn't be asking this.
    I can understand that they are nice to have, but I have been working with Oracle for 15 years without missing them before I first worked with SQL Server and found out that there is such a thing as variables in a SQL query.

    It's always a problem when trying to learn a new piece of (complex) software and trying to apply patterns and best practices from another (complex) piece of software to the "new kid". This will never work. Regardless if you switch from Oracle to SQL Server, from SQL Server to Postgres or any other DB migration. You will always find someone who claims "TaskBlaBla is much easer to do in ProductFoo than in ProductBar. Therefor ProductBar sucks". Most of the time people forget that "TaskBlaBla" should be done in a completely different way in ProductBar.

    But back to the question: If you only need those parameters for "ad-hoc" queries, then usually the SQL Client provides them in the Postgres/Oracle/DB2/whatever world.

    But as you are apparently using Excel as your SQL Client, there is not much you can do. In that case, that parameter table is probably not such a bad idea.

    But maybe we should take a step back, and you tell us which underlying problem you are trying to solve with using Excel as the SQL client (or development "environment"). Maybe the real problem can be solved in a better way.

    Unfortunately, it also doubles the length of time that it takes my queries to run (I have no idea why), so I can't use it regularly.
    Show use the complete query, the complete definition of the tables and most importantly the output of EXPLAIN ANALYZE then I'm sure we will be able to help you.
    See this page for details on what you should supply when asking for help on a slow query: https://wiki.postgresql.org/wiki/Slow_Query_Questions
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  11. #11
    Join Date
    Mar 2015
    Posts
    10
    Thanks for the thoughtful response. I will take a look at that link and see if I can spend some time tonight putting together a response.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •