Question 89263 in PostgreSQL Question posted in
The official documentation can be found here.

How can i implement this code in postgresql using psql?

How would i implement this code in postgresql using psql?

Declare @Id int
Set @Id = 1

While @Id <= 100
Begin 
   Insert Into tblAuthors values ('Author - ' + CAST(@Id as nvarchar(10)),
              'Country - ' + CAST(@Id as nvarchar(10)) + ' name')
   Print @Id
   Set @Id = @Id + 1
End

I’ve tried to search some tutorials for postgresql syntax but none of them works.

Tags:

2

Answers


  1. 0

    No need for PL/pgSQL, a loop or variables:

    insert into tblauthors (name, country)
select concat('Author - ',id),
       concat('Country - ',id,' name')
from generate_series(1,100) as g(id);
    Login or Signup to reply.
  2. 0

    The answer by @a_horse_with_no_name is correct, a single insert is all that is required. However, your question seems more about syntax rather than how to accomplish. So let’s review; some observations necessary to convert your code to Postgres.

    1. The language is plpgsql not psql. Psql in the command line interface application into Postgresql database management system.
    2. @ is not a valid character in an object name.
    3. Declaration of a variable requires specification of the data type.
    4. Set not used for variable assignment. If used it establishes/changes the run-time environment
    5. Variable initialization can occur when variable is defined.
    6. There in no nvarchar data type. Just use text.
    7. There is no Print statement. Use raise notice instead.
    8. + is not used for string concatenation. Use either the function concat() or the operator (||).
    9. Each statement must terminate with a semi-colon (;).
    10. Loops are enclosed by loop ... end loop;
    11. All statements, other than declarations, must be enclosed within
      begin ... end;.
    12. The entire code block must be enclosed in single quotes or a
      Dollar-Quoted String Constant.
    13. All code blocks must be within a Function, a Procedure, a Do block, or within another code block.
    14. While not required it is best to always specify column names on
      Inserts. (IMHO is should be required).

    Accounting for the above your code becomes:

    DO $BLK$          -- A Dollar-Quoted String Constant 
declare 
  id integer = 1;

begin 
    while id <= 100
    loop
        insert into tblauthors (name, country)
             values ( 'author - ' || cast(id as text)
                    , 'country - '  || cast(id as text) || ' name'
                    ); 
        raise notice '%', cast(id as text); 
        id = id + 1;
    end loop;
end; $BLK$;

    Instead of a search some tutorials a good place to start would be the Documentation. More specifically perhaps the chapter Lexical Structure

    NOTE: #2 above is not strictly true. You can us @ in a variable name if it is enclosed in double quotes as "@name". However, double quotes become a read pia very quickly and are best avoided completely.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search