Error: for dblink view

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Objective :

 

1. Create new user (Ex. piam_card ) in postgres database

2. Create view (Ex. attendance ) using dblink functionality. The view will be created in Database 2 & view will have the inner join with database 1 tables

3. The newly created user (piam_Card) must have readonly permission to created view (attendance)

 

Please provide the steps to achieve above functionality.

===============================================================================================================

 

View Defination:

 

CREATE OR REPLACE VIEW view_piam_cardswipes AS

 

 

SELECT DISTINCT events.alarmdiscription AS doorno,

   

replace(to_char(events.controllerdate::time without time zone::interval, 'HH24:MI:SS'::text), ':'::text, ''::text)

AS occurrencetime,

    to_char(events.controllerdate::date::timestamp with time zone, 'ddmmyyyy'::text) AS occurrencedate,

 

    resset.employeeid AS ecno,

    events.idalarm AS "inout",

    events.eventid AS recid,

    events.cardnumber AS event_cardnum,

 

   events.issuelevel,

    events.eventname,

    resset.status

   FROM events

    

 

 

JOIN dblink('port=5432 dbname=PIAMDB', 'SELECT   employeeid, cardnumber as map_cardnum, status

  FROM piamapp.mapping_cardholder_card'::text) resset(employeeid character varying, map_cardnum bigint, status integer)

 

ON events.cardnumber::bigint = resset.map_cardnum 

 

WHERE events.idalarm = 1 OR events.idalarm = 2;

 

ALTER TABLE view_piam_cardswipes

 

 OWNER TO postgres;

 

GRANT ALL ON TABLE view_piam_cardswipes TO postgres;

 

GRANT SELECT ON TABLE view_piam_cardswipes TO piam_card;

GRANT SELECT ON TABLE view_piam_cardswipes TO dblink_regression_test;

 

 

COMMENT ON VIEW view_piam_cardswipes

  IS 'This view return the card swipe entries ';

 

 

 

===============================================================================================================

I have referred below url , but still not able to succeed.

 

http://1command.com/man/?query=dblink_connect&sektion=3&manpath=FreeBSD+9.0-RELEASE+and+Ports

 

Errors:

 

I am getting different errors:

 

1. Below error after using above url setting.

ERROR:  password is required

DETAIL:  Non-superuser cannot connect if the server does not request a password.

HINT:  Target server's authentication method must be changed.

 

2.

 

ERROR: password is required

SQL state: 2F003

Detail: Non-superusers must provide a password in the connection string.

 

The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments.

WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email.

www.wipro.com


[Index of Archives]     [KVM ARM]     [KVM ia64]     [KVM ppc]     [Virtualization Tools]     [Spice Development]     [Libvirt]     [Libvirt Users]     [Linux USB Devel]     [Linux Audio Users]     [Yosemite Questions]     [Linux Kernel]     [Linux SCSI]     [XFree86]

  Powered by Linux