All geek questions in one place

Trying to create Insert function

I am trying to create a function in Postgres 8.4 using pgAdmin and I have the following code

CREATE OR REPLACE FUNCTION spcwriteperson(IN fname varchar(20)) RETURNS VOID AS $BODY$ INSERT INTO person ("firstName") VALUES(fname); $BODY$ LANGUAGE sql VOLATILE COST 100;

when I try to run this, it complains that fname in VALUES (fname) is not a column. I come from writing sprocs and functions in MySQL and Sql Server. Any help on why this is not working, or what am I doing wrong?

+4
source share
2 answers

If you don't like the use of numbered parameters, you can use PL / PGSQL :

 CREATE OR REPLACE FUNCTION spcwriteperson(fname varchar(20)) RETURNS VOID AS $$ BEGIN INSERT INTO person (firstName) VALUES (fname); END $$ LANGUAGE 'plpgsql';

PL / PGSQL will also give you a language similar to SQL Server T-SQL. PL / PGSQL has control structures, variables, and all these fun things. PostgreSQL's SQL implementation is much stricter regarding what functionality is available - it's a great SQL implementation, but it is a query language, not a procedural programming language.

+8
source

Parameters of SQL functions are numbered, $1 , $2 , etc. See the documentation for more details. You can use pgSQL if you need named parameters and more advanced features.

+6
source

Source: https://habr.com/ru/post/1343411/

More articles:


All Articles