CODEWITHSUNDEEP
postgresql
bernie2436
bernie2436

Reputation: 23901

exactly one and two many columns postgres

I am new to postgres from SQL server. I am setting up a function that takes "posID" as an integer and (I think) returns rows from the skills table. If I set the return type of the function to "Skills" I get an error: "final statement returns too many columns." But if I set the return type to "Skills"[] (an array of skills?) then I get the error "final statement must return exactly one column."

I'm confused about what's going on. I thought that postgres was 'seeing' that that jobs id is a primary key on the jobs table and (so expects the function to only return one row) but i get the same errors with or without the primary key--so maybe it is something even more simple.

here is the code in the function

select * from "Skills" inner join "Jobs" on "Skills"."SkillID"="Jobs"."SkillID" where "JobID"="posID"

Here is the jobs table (sometimes without the PK)

CREATE TABLE "Jobs"
(
  "JobID" integer NOT NULL,
  "SkillID" integer,
  "Title" character varying[],
  CONSTRAINT "PK" PRIMARY KEY ("JobID")
)

here is the skills table

CREATE TABLE "Skills"
(
  "SkillName" character varying(50),
  "SkillID" integer
)

here is the function (with different code. obviously it won't accept the code above)

CREATE OR REPLACE FUNCTION "skillsForJob"("posID" integer)
  RETURNS "Skills" AS
'select * from "Skills"'
  LANGUAGE sql VOLATILE
  COST 100;
ALTER FUNCTION "skillsForJob"(integer)
  OWNER TO postgres;

Upvotes: 0

Views: 2164

Answers (1)

Arun P Johny
Arun P Johny

Reputation: 388316

You need to use returns setof <table> to return multiple rows, checkout the documentation

CREATE OR REPLACE FUNCTION skillsForJob(posID integer)
  RETURNS "Skills" AS
'select "Skills".* from "Skills" inner join "Jobs" on "Skills"."SkillID"="Jobs"."SkillID" where "JobID"=$1'
  LANGUAGE sql VOLATILE
  COST 100;
ALTER FUNCTION "skillsForJob"(integer)
  OWNER TO test;

Upvotes: 2

Related Questions