-1
select nextval(:schema.seqname) is this correct syntax?

I am passing the schema name while executing the script by -v schema=s1;

getting an error

ERROR: schema ":schema" does not exist

rashmi
  • 47
  • 1
  • 3
  • 8

1 Answers1

0

Try this:

SELECT NEXTVAL(:'schema'||'.seqname');

This is after a psql line like this:

psql -U user -h host -d dbname -v schema="schemaname"

Bizarre syntax but Craig Ringer's answer to this question led me to the idea: How do you use script variables in psql?

J Spratt
  • 1,762
  • 1
  • 11
  • 22
  • getting an error ERROR: syntax error at or near ":" – rashmi Feb 25 '19 at 19:01
  • Are you using -c also? Or just connecting with -v and then running the query? Can you give an example of your psql line? I tried this solution with success. – J Spratt Feb 25 '19 at 19:07
  • psql -d dbname -U username -f create.sql -v schema=post – rashmi Feb 25 '19 at 19:09
  • So this `SELECT NEXTVAL` is within `create.sql`? – J Spratt Feb 25 '19 at 19:11
  • declare v_id bigint; BEGIN if NEW.empid is null then select nextval((:'schema'||'.empseq') into STRICT v_id ; NEW.empid := v_id; end if; RETURN NEW; End; – rashmi Feb 25 '19 at 19:13
  • That looks like trigger syntax to me, if you're just trying to say "if I didn't supply an empid on insert than default to the next seq_num", you can supply a default in the table creation code. `CREATE TABLE(empid INTEGER NOT NULL DEFAULT NEXTVAL('schema.seqname')`. I'm not sure of the syntax to achieve this with the `-f` or `-c` command. Note: I connected with psql and a variable assigned with `-v` and after that ran the `SELECT` in my answer. – J Spratt Feb 25 '19 at 19:22
  • NextVal takes the single quoted variable.As i am passing as variable not possible to achieve this with the syntax – rashmi Feb 25 '19 at 19:27
  • My only other suggestion would be to try using input redirection rather than the `-f` option. Like this: `psql -d dbname -U user -v schema="post" < create.sql`. I haven't tested this. – J Spratt Feb 25 '19 at 21:27