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

Postgresql – inheritance sql, the subquery must only return one column

i have one mother table Person and 3 daughter tables : EMPLOYEE, PROSPECT, VISITOR.
their is a field name PER_CATEG that contain the category of the person for each row ("EMP" for employee, "PROSP" for prospect and "VIS" for visitor).

i’m using PostgreSQL but anyone that is using sql in general can help.


DROP TABLE IF EXISTS T_PERSONNE;
CREATE TABLE T_PERSONNE(
    PER_ID INT GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1),
    PER_NOM VARCHAR(50),
    PER_PRENOM VARCHAR(50),
    PER_DATE_NAISSANCE date,  
    PER_LOGIN VARCHAR(50),
    PER_MDP VARCHAR(50),
    PER_CATEG CHAR(5), -- EMP / PROSP / VIS
    CONSTRAINT PK_PERSONNE PRIMARY KEY (PER_ID)
);

DROP TABLE IF EXISTS T_EMPLOYE;
CREATE TABLE T_EMPLOYE(
    EMP_ID int,
    EMP_FONCTION VARCHAR(50),
    EMP_DATE_EMBAUCHE date,
    EMP_SALAIRE_EURO float,
    EMP_EST_DIRECTEUR boolean,
    CONSTRAINT PK_EMPLOYE PRIMARY KEY (EMP_ID),
    CONSTRAINT FK_PERSONNE_EMPLOYE FOREIGN KEY (EMP_ID) REFERENCES T_PERSONNE (PER_ID)
);

DROP TABLE IF EXISTS T_PROSPECT;
CREATE TABLE T_PROSPECT(
    PROSP_ID int,
    PROSP_FONCTION VARCHAR(50),
    PROSP_DATE_EMBAUCHE date,
    PROSP_SALAIRE_EURO float,
    PROSP_NOMBRE_TOTAL_VENTE int,
    PROSP_BENEFICE_TOTAL_VENTE_EURO float,
    PROSP_EST_DIRECTEUR boolean,
    CONSTRAINT PK_PROSP PRIMARY KEY (PROSP_ID),
    CONSTRAINT FK_PERSONNE_PROSPECT FOREIGN KEY (PROSP_ID) REFERENCES T_PERSONNE (PER_ID)
);

DROP TABLE IF EXISTS T_VISITEUR;
CREATE TABLE T_VISITEUR(
    VIS_ID int,
    VIS_PROFESSION varchar(50),
    VIS_DATE_INSCRIPTION date,
    VIS_NOMBRE_VISITE int,
    VIS_DATE_DERNIERE_VISITE date,
    VIS_REVENU_EURO float,
    CONSTRAINT PK_VISITEUR PRIMARY KEY (VIS_ID),
    CONSTRAINT FK_PERSONNE_VISITEUR FOREIGN KEY (VIS_ID) REFERENCES T_PERSONNE (PER_ID)
);

the mother table contain only the fields that are common to the daughter tables. the daughter tables contain only the fields that are not common to the three of them exept the id that is the same for the mother table and their daughters.

what is the best way to get every field of one person and his category table Only knowing his id in one single query?

i tried a "select swtich case" but i can only get one column for the daughter table.

here is the code :

SELECT *,
       CASE WHEN PER_CATEG='EMP' THEN (select * from T_EMPLOYE where EMP_ID = 1)
            WHEN PER_CATEG='PROSP' THEN (select * from T_PROSPECT where PROSP_ID = 1)
            WHEN PER_CATEG='VIS' THEN (select * from T_VISITEUR where VIS_ID = 1)
            ELSE FALSE
       END
FROM T_PERSONNE 
WHERE PER_ID = 1;

here is the error message : "the subquery must only return one column".

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    So, I tried the LEFT JOIN solution which is working well, but I thought that maybe a stored function as someone else suggested would be more convenient.

    So I tried this other one, but then I got a syntax error that I didn't find any solution yet.

    Here is the code I wrote :

    drop function if exists get_person_and_subtable(integer);

CREATE OR REPLACE FUNCTION get_person_and_subtable(IN id integer)
RETURNS SETOF record 
AS $fun$

DECLARE var_categ CHAR(5); 
DECLARE var_id INT = id;

BEGIN
    var_categ := (select PER_CATEG from T_PERSONNE where PER_ID = var_id); -- sensible à la casse, uniquement en minuscule
    IF var_categ ='EMP' THEN RETURN QUERY SELECT * FROM T_PERSONNE INNER JOIN T_EMPLOYE ON T_PERSONNE.PER_ID = T_EMPLOYE.EMP_ID;
    ELSE IF  var_categ ='PROSP' THEN RETURN QUERY SELECT * FROM T_PERSONNE INNER JOIN T_PROSPECT ON T_PERSONNE.PER_ID = T_PROSPECT.PROSP_ID;
    ELSE RETURN QUERY SELECT * FROM T_PERSONNE INNER JOIN T_VISITEUR ON T_PERSONNE.PER_ID = T_VISITEUR.VIS_ID; 
    END;
    $fun$ 
    LANGUAGE plpgsql;

    here is a translation of the error message I got: ERROR: syntax error near« ; » LINE 15: END;

    I tried different syntax modification that I saw on this forum to solve the problem, but it did not work.


  2. 0

    LEFT JOIN all three tables to T_PERSONNE and use CASE WHEN for selecting which of your data columns to show. But you wil definitely have to find some common list of columns, that satisfy all of your depending columns to be contained. In some situations, some columns will have to contain NULL, and the (application) code that uses the result has to care for deciding, which of the non-common columns to use and how.

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

Back To Top
Search