How to make auto increment in database and return to user

Question

0.00/5 (No votes)

See more:

i have database with field called schcode for state.when user wants to enter new code it give auto incremented code.How can i achieve it

What I have tried:

CREATE FUNCTION fngetlastcodeofvillage( IN districtname text,
IN talukname text,
IN villagenam text) RETURNS setof text AS
$BODY$
BEGIN
SELECT schcd FROM villagerural_school_form where districtna=$1 and talukname=$2 and villagenam=$3 order by id desc limit 1
IF schcd IS NULL THEN
RETURN census2011||01 from villagerural where districtna=$1 and talukname=$2 and villagenam=$3
else
return schcd+01 from villagerural_school_form where districtna=$1 and talukname=$2 and villagenam=$3
END IF;

END;

$BODY$
LANGUAGE sql VOLATILE
COST 100
ROWS 1000;
ALTER FUNCTION fngetlastcodeofvillage(text, text, text)
OWNER TO postgres;

I want to do some thing like above can you tell whats wrong in the above function and make it wotkout

Posted 2-Apr-16 1:40am

Veena Hosur

Updated 4-Apr-16 1:46am

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2016-04-02T02:03:00

Solution 1

See here: PostgreSQL - AUTO INCREMENT[^] and here: PostgreSQL: Documentation: 9.5: Sequence Manipulation Functions[^]

Posted 2-Apr-16 2:03am

OriginalGriff

CHill60 · Answer 2 · 2016-04-02T02:29:00

An alternative to using SERIAL or SMALLSERIAL columns in the table would be to create a sequence. For example both these tables below end up with identical data

SQL

create table t1
(
  id SERIAL,
  someData varchar
);

CREATE SEQUENCE t3_id_seq;
CREATE TABLE t3 (
  id smallint NOT NULL DEFAULT nextval('t3_id_seq'),
  someData varchar
);
ALTER SEQUENCE t3_id_seq OWNED BY t3.id;

insert into t1 (someData) values ('one');
insert into t1 (someData) values ('two');
insert into t1 (someData) values ('three');

insert into t3 (someData) values ('one');
insert into t3 (someData) values ('two');
insert into t3 (someData) values ('three');

The disadvantage of this method is that you would require a sequence of each table that had such an identity column (which is why I included the table name in the sequence name)

One advantage of using sequence over SERIAL could be that you can use the LastVal() function against the sequence to find out which was the last id inserted (if you wanted to update your GUI immediately)

But there is an even neater and more efficient way to do that (from version 8.2) using the RETURNING version of the insert statement (ref: PostgreSQL: Documentation: 9.1: INSERT[^])
E.g.

SQL

insert into t1 (someData) values ("four") RETURNING id