Thread: selects possible in VALUES() claueses?

Is it possible to do this?

INSERT INTO Usrs(
  first_name,
  middle_name,
  sur_name,
  sur_name_extra,
  gen_id,
  login
)
VALUES (
  'Dennis'::text,
  'Keith'::text,
  'Gearon'::text,
  'none'::text,
  (SELECT gen_id FROM Gens WHERE gen = 'none'::varchar),
  'dude'::varchar
);

It's puking with the following error:

Error - /phpPgAdmin/path/and/file -- Line: 112

PostgreSQL said: ERROR: parser: parse error at or near "SELECT"
Your query:
INSERT INTO Usrs(
first_name,
middle_name,
sur_name,
sur_name_extra,
gen_id,
login
)
VALUES (
'Dennis'::text,
'Keith'::text,
'Gearon'::text,
'none'::text,
(SELECT gen_id FROM Gens WHERE gen = 'none'::varchar),
'dude'::varchar
)
--

Carpe Dancem ;-)
-----------------------------------------------------------------
Remember your friends while they are alive
-----------------------------------------------------------------
                         Sincerely, Dennis Gearon

On Sun, Mar 02, 2003 at 19:05:15 -0800,
  Dennis Gearon <gearond@cvc.net> wrote:
> Is it possible to do this?

Yes but you need to use 'select' instead of 'values' the query should
return the constant strings in addition to gen_id. My guess is that
you might want a limit 1 in there too so that only one gen_id is
returned.

On Sun, Mar 02, 2003 at 07:05:15PM -0800, Dennis Gearon wrote:
> Is it possible to do this?

No. VALUES only works with real plain values. However, SELECT will work,
like so:

> INSERT INTO Usrs(
>   first_name,
>   middle_name,
>   sur_name,
>   sur_name_extra,
>   gen_id,
>   login
> )

SELECT

>   'Dennis'::text,
>   'Keith'::text,
>   'Gearon'::text,
>   'none'::text,
>   (SELECT gen_id FROM Gens WHERE gen = 'none'::varchar),
>   'dude'::varchar
;

Hope this helps,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Support bacteria! They're the only culture some people have.

Hmm,
    It works inside of a stored procedure. But I will use SELECT on the
command line and see how it goes.

--

Carpe Dancem ;-)
-----------------------------------------------------------------
Remember your friends while they are alive
-----------------------------------------------------------------
                         Sincerely, Dennis Gearon

Martijn van Oosterhout <kleptog@svana.org> writes:
> On Sun, Mar 02, 2003 at 07:05:15PM -0800, Dennis Gearon wrote:
>> Is it possible to do this?

> No. VALUES only works with real plain values.

Really?

regression=# create table foo (f1 text, f2 int);
CREATE TABLE
regression=# insert into foo values ('abc', (select max(unique1) from tenk1));
INSERT 291603 1
regression=# select * from foo;
 f1  |  f2
-----+------
 abc | 9999
(1 row)


But INSERT ... SELECT is just as good a solution, if not better.

            regards, tom lane

I discovered that it's my test trigger on this table that is causing the
problem.

I've now fixed it, seems the parser needs a set of () around a SELECT
statement to allow the value of that statement to be used in another
expression.

What I've been trying to do is  to find out if the NEW value a trigger
sees is actually used as part of the database, i.e. is visible to
statements inside of the trigger for the NEW INSERT. It seems that they
are not. If I've figured this out right, the 'versioning snapshot'is all
the data that a trigger will see inside of it, if it's a BEFORE trigger.

Will a guru please check my assumptions based on below?

This trigger and fuction only allows an INSERT when the number of rows
in the table is:

    GREATER THAN OR EQUAL to 3

    BEFORE the INSERT that triggers the FUNCTION.

    So I see that as proving that the select statement in the
    function does not see NEW (record) in the Usrs table?

--------------------------------------------------------------------
CREATE OR REPLACE FUNCTION test_tgr_on_usrs () RETURNS OPAQUE AS '
   DECLARE var_tmp INTEGER;
   BEGIN
        var_tmp := (SELECT COUNT(*) FROM Usrs);
    IF var_tmp < 3 THEN
       RETURN NULL;
    ELSE
       RETURN NEW;
    END IF;
   END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER tgr_on_usrs BEFORE INSERT ON Usrs
   FOR EACH ROW
   EXECUTE PROCEDURE test_tgr_on_usrs();



Carpe Dancem ;-)
-----------------------------------------------------------------
Remember your friends while they are alive
-----------------------------------------------------------------
                         Sincerely, Dennis Gearon

Dennis Gearon <gearond@cvc.net> writes:
>     So I see that as proving that the select statement in the
>     function does not see NEW (record) in the Usrs table?

It's a BEFORE trigger.  The insert hasn't happened yet.

            regards, tom lane